# Thread: Broken VLookup Thanks:  1 Post #4990871 (1) Likes: 0

1. ## Broken VLookup

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!

2. ## Re: Broken VLookup

Hi. What does broken mean? It would be helpful to see the formula and what you think is broken about it.

3. ## Re: Broken VLookup

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

4. ## Re: Broken VLookup

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.

Sheet1

 A B G H I 1 Emp. Name Supr. 2 Bart A Mack 3 Bart A Mack 4 Bart A Mack 5 6 Kyle A Mack 7 Kyle A Mack 8 9 Dane B Jerrey 10 Dane B Jerrey 11 Dane B Jerrey

 Cell Formula I2 =IF(\$A2="", "",INDEX(Sheet2!\$A\$2:\$A\$4,MATCH(\$A2,Sheet2!\$B\$2:\$B\$4,0)))

Sheet2

 A B 1 Sup. Name Empy. Name 2 Mack Bart A 3 Mack Kyle A 4 Jerrey Dane B

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: Broken VLookup

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

6. ## Re: Broken VLookup

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

7. ## Re: Broken VLookup

That's giving me a NAME? error.

8. ## Re: Broken VLookup

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

9. ## Re: Broken VLookup

=if(A2="","",VLOOKUP(A2,VL_Data!A:A,VL_Data!I:I,FALSE).
Copy down.

It is best not to reference whole columns, and reference only the range that holds your data.
The first argument in VLOOKUP(Lookup value) can only reference one cell not a range.

10. ## Re: Broken VLookup

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.