VLookup Using Two Values In Two Columns
Aug 11, 2014
I am creating a payroll spreadsheet that will look up tax deductions from a spreadsheet I pulled from the IRS website. The first two columns represent a range of values the employee's pay would fall between. The first column is "paid at least" and the second column is "but paid less than".
Once I locate which row the employee's wages fall between, I would then need to match their number of withholdings with the corresponding column. Columns 3, 4 and 5 have headings to represent 0, 1 or 2 withholdings.
The value I need returned would be the intersection of wages paid and withholdings. For my spreadsheet example, if the employee's wages are 1,023 and they have 1 withholding, I need the formula to return 147. I was able to find a vlookup/match formula but it worked only without a range of wages paid.
For example, if the employee was paid exactly 1,030 and had 1 withholding, it would vlookup/match to return 147. Obviously, this won't work with me needing to find the correct row based on where their wage falls in the ranges.
I've attached a picture of the spreadsheet example below. payroll example.jpg
View 2 Replies
ADVERTISEMENT
Apr 13, 2013
I need a User Defined Function (UDF) to Vlookup duplicate values in separate Columns.
I can't use an Array function as the data is huge and it takes lot of time to calculate.
I have attached a Input and the desired output in the file attached.
UDF_MACRO.xlsx
View 9 Replies
View Related
Apr 18, 2014
I have attached a file with an example of what i am looking for. The data in columns A and B are my original Data. There are multiple instances of e-mail address on the left but I need to bring in each data horizontally. I would like a formula to enter into E2:J6 to pull the information in.
Book2.xlsx
View 9 Replies
View Related
Feb 26, 2013
Vlookup or Match for a product list project.
I need "my sheet" to populate (columns A,B,&C) with some source data; however, I need the lookup to reference both retailer# and color (columns D&E) and return other information such as description, color description, etc...
Here is the example of both sheets:
My Sheet:
Row/Col.
A
B
C
[Code]....
View 1 Replies
View Related
Oct 6, 2009
My Data
Service 1 | 100 | 1.5$
Service 1 | 200| 2.5$
Service 1 | 300| 3.5$
Service 2 | 100| 11.5$
Service 2 | 125| 21.5$
Service 2 | 300| 30$
I need to extract the value in the third column according to an exact match of the first column (the service name) and a range match of the third column.
I tried SumProduct but the problem with my formula was:
Let's assum I ask for
Service 1, 200
The formula summed up 1.5 and 3.5 and returned 5
When I wanted to return one value from this table.
View 9 Replies
View Related
Jun 4, 2012
I have a spreadsheet (Excel 2010). I want to fill categoryid in Sheet One based on values of Skill and State which are part of field in Sheet two.
Sheet One (Has Four Columns and I am looking for filling CategoryID based on Sheet Two
FirstName LastName Skill State CategoryID
John Edward Ballet California
Ed Catalino Tap London
Natasha Curtis Ballet Australia
Shen Watson Modern Kansas
Sheet Two
CategoryID CategoryDescription
1 Dancers/Ballet/United States/Alaska
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdom/Wales
4 Dancers/Ballet/UnitedKingdom/London
5 Dancers/Tap/United States/Alaska
6 Dancers/Tap/United States/California
7 Dancers/Tap/United Kingdom/Wales
8 Dancers/Tap/United Kingdom/London
9 Dancers/Ballet/Australia
10 Dancers/Modern/United States/Kansas
View 5 Replies
View Related
Apr 30, 2014
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
View 1 Replies
View Related
Jul 23, 2014
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
View 8 Replies
View Related
Oct 27, 2008
i want to put a subject for a negative values, how should i do it?
For example:
lookup_value: -1000
table_array:
<0 - Poor
0-10000 - Good
> 10000 - excellent
is it possible to do this? ALternatively see the (new) attachment for clearer question.
View 8 Replies
View Related
Oct 13, 2008
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
View 4 Replies
View Related
Mar 4, 2009
I am trying to do a vlookup function of 2 values but the key lookup column is an approximation.
I have attached a sample data file. Essentially, on Sheet 1 Column M (labeled as EPS), I want to do a vlookup of both Column A and B, and copy the values in Sheet 2 Column K into Column M. The approximate value comes from Column B, the date, as Sheet 1 has all the dates from 2001 and 2008 while Sheet 2 only has 4 dates per year. On sheet 2, the first 2 dates are 10/30/2001 and 2/5/2002. I want the EPS value for 10/30/2001 to stand for all values between 10/30/2001 and 2/5/2002.
The Vlookup function with a True value for the last criterion can do this for a vlookup of 1 variable, but it doesn't work for 2.
View 10 Replies
View Related
Mar 26, 2014
I have set of data in multiple range ,need to fill the and replace the old values depends upon two column values (AH & AL)
IF Active Calls is "TATA" In AH:AH, and IF Action Onwer Col is "Blank",in AL:AL
Then Fill the Blank cells by Values "SVC" in the col Action Owner,Then Replace Old values by "Updates Awaited" in Status Col(AM:AM)
Find the attachment & basic code take this code for this task
[Code] ....
toggle-2.xlsb
View 5 Replies
View Related
Mar 14, 2014
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
View 2 Replies
View Related
May 15, 2014
I would like to align the matching values in columns A and B but I want the values that correspond to b in c,d and e to go with the column b value.
C D and E don't necessarily have a value in there but if it does it needs to move with B
View 5 Replies
View Related
Aug 20, 2014
I'm looking for a vlookup that will look in 2 columns of the array table and output an answer based on them.
See the attached example : Example.xlsx
View 5 Replies
View Related
Jan 13, 2014
I have the following concern as i'm not able to solve it alone:
Ex: =vlookup(c11;flyingprogram;2+5;false)
View 4 Replies
View Related
Feb 21, 2013
Code:
A B C D
1 2801 2 2816 1
2 2802 2 2815 0
3 2803 3 2814 88
4 2804 4 2813 99
5 2805 5 2812 2
6 2806 6 2811 6
7 2807 4 2810 1
8 2808 3 2809 4
I'm new here and I saw that many people actually have excellent knowledge in excel.
Well, I'm trying to use vlookup on my sheet, but the data I have comes with the code and your score his side.
In a standard vlookup would be: = vlookup (value, range, 2, false).
This is the problem, I would like that when he could not find the value in column A or B, were to column C or D and so on.
View 9 Replies
View Related
Jul 3, 2009
I have a spreadsheet with the following data:
A B C D E
18500 Eggs 4 6 9
18500 Bread 2 0 1
18500 Cheese 3 8 4
71200 Eggs 9 6 3
71200 Bread 2 8 4
71200 Milk 1 0 8
I need a formula to check column A, then B and return a result from C:E.
Column A is 18500 or 71200
Column B is Eggs, Bread, Milk or Cheese
Column C, D E are sales figures.
View 9 Replies
View Related
Apr 17, 2009
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
View 7 Replies
View Related
Apr 21, 2014
I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.
Let's say I start with a table that looks like this:
A
B
C
D
Dept
E
Account
T
F
F2
G
Debit
Credit
Total
33010
[Code] ....
I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:
A
B
C
D
Dept
E
Account
[Code] .........
Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.
View 8 Replies
View Related
Jun 10, 2014
I've used VLOOKUP based on matching one column and returning one result. No problem. Now I need to return one result based on 3 columns matching.
Assume this range of columns is named: TravelDays
Origin...Location...ShipVia...TravelDays
33778...420...UPS...3
33778...425...UPS...1
33778...440...UPS...3
33778...443...UPS...3
33778...446...UPS...2
33778...447...UPS...3
33778...449...UPS...5
33778...475...UPS...3
What I'm trying to accomplish is if these columns match:
i2=Lookup!TravelDays,1
E2=Lookup!TravelDays,2
N2=Lookup!TravelDays,3
My result should be: Lookup!TravelDays,4
View 14 Replies
View Related
Oct 26, 2009
I need a Vlookup to look at two columns when searching. here is an example:
Sheet 1 is my report grid to hand out to the employees, A1 has a drop-down list with my employees names and A2 one for the month, when you click an employee name the vlookup formula fills in the information from sheet 2 (data sheet).
Sheet 2 has data like: Col 1= Month, col 2=employee L-name, col 3= production etc...
currently the vlookup formula messes up because the employee name is in there multiple times because of the months column, i have to delete all months except the one i need. formula is: =vlookup(A1,'sheet2'!,B:C,2,false)
this formula will pull the data in sheet two column 3 (production) for the employee that matches.
how can i make it compare first to col 1 (month) and then col 2 (employee).
View 10 Replies
View Related
Dec 16, 2012
I have a list of names I need to look up against another list. My problem is that the second list has a mixed formatting of Firstname Lastname and Lastname Firstname. What I did so far is create a second column that reverses the first and last names in the column.
Table1:
A
B
1
Name
Name (reversed)
2
John Smith
Smith John
3
Jackson Laura
Laura Jackson
4
Maxwell Ted
Ted Maxwell
5
Chris Hansen
Hansen Chris
Table2:
A
B
1
Name
Currently employed?
2
John Smith
=IF(ISNA(VLOOKUP([A1; Table1!A:A;1;FALSE));"NO"; "YES")
3
Laura Jackson
4
Ted Maxwell
5
Chris Hansen
The VLOOKUP formula works great but I need it to look for a matching name across Table1:A:B not just Table1:A:A. How would I do this?
View 2 Replies
View Related
Sep 23, 2008
I have two columns with names on it. and Column A has about 600 names and column B has about 447 names. I want to match these two columns and get the names which do not match on Column C.
View 11 Replies
View Related
May 21, 2009
=IF(ISERROR(VLOOKUP(70036,PositionDataBuy!$D$2:$U$505,17,FALSE)),"Not Found", VLOOKUP(70036,PositionDataBuy!$D$2:$U$505,17,FALSE))
To remove the error if Vlookup can't find a match - the formula above uses the ISERROR.
If vlookup produces an error (can't find a match) the Excel cell returns "Not Found", otherwise, it returns a valid Amount from column 17.
This formula is inserted into an Excel specific location using Access VBA based on a set returned from a SQL Server Query.
An Excel Worksheet PositionDataBuy is created in sort order of Contract numbers.
The problem came a few days later when a condition of 3 of the same Contract numbers for the same day had different buy Amounts.
So the Vlookup found the first contract number (e.g. 70036) and the value associate with the first instance in PositionDataBuy three times)
The challange is now to use Vlookup for the contract number, then (if it matches) look over at the Amounts column and determines that from each contract record (assuming a duplicate)
it also matches the volume.
So, if Vlookup's error message is False (found first value), another nested if (with the ISERROR) will check the second required column.
But, what if the valid column is on the second record with 70036? I suspect if an error was generated the Vlookup is done and it won't continue.
The data below is fudged, lets assume that no two volumes would be the same for now.
Example: 70036 also needs to be tested against volume and then return the Amount
Contract Volume Amount
70035 5 100
70036 7 120
70036 3 33
70036 6 22
70037 1 11
View 13 Replies
View Related
Jul 20, 2011
i am trying to get a VLOOKUP to work over a number of different columns. An example the problem:
Sheet1
A B
1 10
2 20
3 30
4 40
5 50
Sheet1 is the sheet i want to lookup the values and i want to place the results into column B
Sheet2
A B C
1 10 40 50
2 20
3 30
Sheet2 contains all the infromation that i want to look for to make the comparison.
I know that the expression used for the VLOOKUP is:
Code:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
The issue i have is "col_index_num" as i want to look over multipule columns. I have been using this expression:
Code:
=VLOOKUP(A2,Sheet2!$A$1:$J$20000,1,FALSE)
How do i change the "col_index_num" to look over all 3 columns and not just the 1st, 2nd, or 3rd?
View 5 Replies
View Related
Apr 10, 2012
The row in blue is a successfully created result from my VLOOKUP formula:
The row in red is unsuccessful.
=VLOOKUP($A$11,$A$3:$G$9,(COLUMN($B$2)))
The above formula lives in the blue "Pottery" cell (cell B11 in my spreadsheet).
How can I get this VLOOKUP formula to work in rows below it?
When I just copy and paste the formula into row 12 (in red text), the formula still refers to row 11 (in blue text), and I get the same exact results no matter what unique signifier I put in (see the red row for unsuccessful results).
It's strange because in the successful blue row, I simply copied and pasted from cell B11 through G11, and everything came out fine due to the relative(??) identifiers, but no luck transferring that formula to rows.
My unique identifiers seem to be off the page, but they are just 1 (starting at Old Masters) through 6 (at Impressionist).
When I type 6 (for the red row), I get the same results as for when I type 5 (for the blue row), so my unique identifiers aren't registering somehow.
Item Code
Dept
Description
Qty.
Price
In stock
Line Total
1
Old Masters
Painting
100
5
2
New Masters
Sculpture
200
10
[Code] ........
View 9 Replies
View Related
Aug 19, 2013
Basically, I have an array of data (let's say A2:D20). I then want to do a VLOOKUP across the columns A:D, and return the corresponding value in column G, if VLOOKUP can find a particular value from column F, say.
So, I need a VLOOKUP function written in G2, that finds the value E2 in EITHER A2,B2,C2 or D2 (and return what is in E2, say).
I first thought I could CONCATENATE the strings in A2, B2, C2 and D2 and use some sort of VLOOKUP that finds a string within another string.
View 2 Replies
View Related
Sep 21, 2013
I have two separate worksheets, and I am trying to create a Vlookup or Index and Match formula. Here is the example:
Sheet 1
Cell A1= Employee ID: 123-D.
Sheet2
Vlookup A1 from Sheet 1, and match the first five characters to Column A, Column I and Column P. If a match, return name (e.g. John Doe) in Sheet 1, cell B1.
View 9 Replies
View Related
Apr 10, 2014
Not sure where to start with this one, an IF statement or vlookup? Basically I have 3 columns A, B, C (original sheet) and A, B, C in my working sheet. A contains car registrations, B contains a date and C contains a weight. I need to return C from my original sheet into my working sheet only when A and B BOTH match from my working sheet (there are scenarios where the same car registration has a different date next to it). So I want to return the weight only when the car registration and the date match in a specific instance from my working sheet.
View 6 Replies
View Related