Hello,
I am trying to create a vlookup for employee data, based on a number code match using the employee # as the lookup value. Unfortunately, my data has multiple codes for the same employee number, and isn't populating the lookup correctly.
Here are my formulas in separate columns on sheet 2. B4 is on sheet 2 and is the employee # being matched to Sheet 1 column D
=IFERROR(IF('Sheet 1'!A:A=7700,VLOOKUP($B4,'Sheet 1'!$D:$H,3,FALSE),""),"")
=IFERROR(IF('Sheet 1'!A:A=7701,VLOOKUP($B4,'Sheet 1'!$D:$H,3,FALSE),""),"")
=IFERROR(IF('Sheet 1'!A:A=7702,VLOOKUP($B4,'Sheet 1'!$D:$H,3,FALSE),""),"")
=IFERROR(IF('Sheet 1'!A:A=7704,VLOOKUP($B4,'Sheet 1'!$D:$H,3,FALSE),""),"")
The data below is from Sheet 1.
|
A
Deduction |
B
Deduction Desc |
C
Employee Name |
D
Employee # |
E
Org Code |
F
Employee Amount |
G
Employer Amount |
H
Employee Gross |
| 7700 | MPERS TEACHERS EMPLOYEE | LV | 100012 | 251090 | 218.02 | 113.14 | 2,850.00 |
| 7700 | MPERS TEACHERS EMPLOYEE | BG | 100015 | 110013 | 418.12 | 216.98 | 5,465.68 |
| 7700 | MPERS TEACHERS EMPLOYEE | CL | 100059 | 240009 | 254.75 | 132.20 | 3,330.00 |
| 7700 | MPERS TEACHERS EMPLOYEE | LL | 100069 | 222030 | 207.70 | 107.80 | 2,715.00 |
| 7700 | MPERS TEACHERS EMPLOYEE | DC | 100078 | 240039 | 232.56 | 120.68 | 3,040.00 |
| 7700 | MPERS TEACHERS EMPLOYEE | AL | 100081 | 112010 | 373.56 | 193.86 | 4,883.08 |
| 7700 | MPERS TEACHERS EMPLOYEE | HA | 100093 | 110007 | 400.62 | 207.90 | 5,236.92 |
| 7700 | MPERS TEACHERS EMPLOYEE | AJ | 100123 | 110013 | 373.56 | 193.86 | 4,883.08 |
| 7700 | MPERS TEACHERS EMPLOYEE | CM | 100139 | 251090 | 420.40 | 218.16 | 5,495.32 |
| 7701 | MPERS TCH FED FUNDED EE/ER | ND | 100195 | 2055080 | 400.62 | 819.58 | 5,236.92 |
| 7704 | OFFICE OF STATE EMP HLTH | ND | 100195 | 2055080 | 0.00 | 207.90 | 5,236.92 |
| 7700 | MPERS TEACHERS EMPLOYEE | MM | 100254 | 110009 | 0.00 | 0.00 | 0.00 |
| 7701 | MPERS TCH FED FUNDED EE/ER | MM | 100254 | 110009 | 136.55 | 279.35 | 1,785.00 |
| 7704 | OFFICE OF STATE EMP HLTH | MM | 100254 | 110009 | 0.00 | 63.72 | 1,785.00 |
| 7700 | MPERS TEACHERS EMPLOYEE | CJ | 100282 | 210011 | 412.10 | 213.86 | 5,386.92 |
| 7701 | MPERS TCH FED FUNDED EE/ER | CJ | 100282 | 210011 | 0.00 | 0.00 | 0.00 |
| 7704 | OFFICE OF STATE EMP HLTH | CJ | 100282 | 210011 | 0.00 | 0.00 | 0.00 |
| 7700 | MPERS TEACHERS EMPLOYEE | ML | 100296 | 250090 | 332.80 | 172.72 | 4,350.40 |
| 7700 | MPERS TEACHERS EMPLOYEE | MCM | 100384 | 120030 | 453.89 | 235.55 | 5,933.08 |
| 7701 | MPERS TCH FED FUNDED EE/ER | MCM | 100384 | 120030 | 0.00 | 0.00 | 0.00 |
| 7704 | OFFICE OF STATE EMP HLTH | MCM | 100384 | 120030 | 0.00 | 0.00 | 0.00 |
| 7700 | MPERS TEACHERS EMPLOYEE | MCM | 100400 | 120030 | 400.62 | 207.90 | 5,236.92 |
Hello Daneille,
Please take a look at this nice blog post, you will find your answer there.
https://www.myonlinetraininghub.com/vlookup-using-dates-multiple-values-in-multiple-columns
In regard to the linked article, I find the Index & Match solution presented at the end easier.
Hi Danielle
Are you trying to extract multiple records for the same employee number?
e.g. Employee#100282 will display 3 records as per your example above.
You can take a look at the article here : https://www.myonlinetraininghub.com/excel-factor-17-lookup-and-return-multiple-matches
You can also consider using a PivotTable.
If the suggestions of Anders and I are not what you wanted then please attach your file with the expected result.
It will help us understand your needs better.
Sunny
Unfortunately, I cannot attach the spreadsheet, as it has sensitive information.
I cannot use a pivot table for this information, as I need it to feed into my master sheet.
I was unable to get an index match formula to work, and would like some help. The issue I'm running into, is that the formula is finding the employee number for 7700 only, and will not populate for 7701, 7702 or 7704.
Hello,
Please see attached example.
Hi Danielle
If you are not able to supply the file them maybe you can supply the output format based on your sample data above.
At the moment we can only make a guess what is would look like and if it is not correct a lot of effort will go to waste.
The actual solution will depend on your final output format.
Let us know what version of Excel you are using as certain Functions may not available in all versions.
Sunny
Amazingly Anders, that worked! thank you, thank you! Amazing!
Hello Danielle,
You are welcome.
What I find amazing is all the knowledge stored and made available here at MOTH. The blog articles contains lots of helpful information.
