Broken VLookup

kscwgirl

New Member
Joined
Jan 19, 2018
Messages
9
I have a spreadsheet where the data is on 2 tabs and the vlookup has been broken. The person in this position before me didn't bother to fix it and now I'm having trouble doing so. Is anyone willing to help out? I think an INDEX MATCH is better in this case and I've been trying to do one but I can't get it to work either. Thank you!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi. What does broken mean? It would be helpful to see the formula and what you think is broken about it.
 
Upvote 0
Unfortunately I can't tell you the answer to that. The previous girl had deleted the formula and told me it was broken. I got one Index to work but couldn't get the match to work. I would rather not do 70 different index formulas, I think either an index match or vlookup would be easier. I have screenshots of the spreadsheet and the current NA error on the match. The idea is that it matches the employee name on the first tab to the employee and supervisor name on the 2nd tab and then fills in the supervisor name column on the first tab (the yellow boxes)

https://ibb.co/dx9bzG
https://ibb.co/neRZmw
https://ibb.co/iTAieG
 
Upvote 0
See if this example helps.
Copy formula down as needed.

VLOOKUP will not work the way your table is set up on sheet 2. VLOOKUP can't look to the left, so you would need the table set up with Emp. Name and then Sup. Name.

Excel Workbook
ABGHI
1Emp. NameSupr.
2Bart AMack
3Bart AMack
4Bart AMack
5
6Kyle AMack
7Kyle AMack
8
9Dane BJerrey
10Dane BJerrey
11Dane BJerrey
Sheet1
Excel Workbook
AB
1Sup. NameEmpy. Name
2MackBart A
3MackKyle A
4JerreyDane B
Sheet2
 
Upvote 0
Thank you, that helped for the first employee but all others are giving me an N/A Error. I'm pretty determined to get this fixed LOL

I moved the columns on the 2nd sheet. Can you tell me what is wrong with my vlookup? Employee Name is now Column A and Supervisor Name is Column B on the VL_Data sheet

9eIV7Xy.jpg
 
Upvote 0
kscwgirl,

Give this a try...

=IFERROR(INDEX(VL_Data!B:B,MATCH($A2,VL_Data!A:A,0)), "NoMatch")
=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))



Don
 
Upvote 0
kscwgirl,

Did you remember to put the ! at the end of your VL_Data sheet name in the formula? So it should be VL_Data!

Looking at the picture of your data in Sheet1 and your explanation that the VL_Data! sheet has employee names in A:A and supervisor name in column B, this formula should work. Does employee name in Sheet1 match the name in VL_Data! exactly, if not you would be getting a No Match error tho'. Works on my sample worksheets....

Put formula in Sheet1 I2 and copy down
=IFERROR(INDEX(VL_Data!B:B,MATCH($A2,VL_Data!A:A,0)), "NoMatch")

Explanation of Index
=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

Don
 
Upvote 0
I have never thought of myself as being bad at excel until this spreadsheet. It's enough to make me want to throw things.

I copied that formula above and this is what I get. I promise I am not normally this dense. Just something about vlookups I do not get.

S4jiQub.jpg
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top