Log In
Register
Home
Active Member
January 11, 2019
Offline
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 |
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello Daneille,
Please take a look at this nice blog post, you will find your answer there.
https://www.myonlinetraininghu.....le-columns
In regard to the linked article, I find the Index & Match solution presented at the end easier.
VIP
Trusted Members
June 25, 2016
Offline
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.myonlinetraininghu.....le-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
Active Member
January 11, 2019
Offline
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.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Please see attached example.
Answers Post
VIP
Trusted Members
June 25, 2016
Offline
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
Active Member
January 11, 2019
Offline
Amazingly Anders, that worked! thank you, thank you! Amazing!
VIP
Trusted Members
December 7, 2016
Offline
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.
1 Guest(s)
