Broken VLookup

[kscwgirl]

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!

 

[steve the fish]

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

 

[kscwgirl]

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

 

[AhoyNC]

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
	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
Sheet1

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

 

[kscwgirl]

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

 

[ddander54]

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

 

[kscwgirl]

That's giving me a NAME? error.

 

[ddander54]

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

 

[AhoyNC]

Change your lookup to:
=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.
See this link:
https://support.office.com/en-us/ar...pId=xlmain11.chm60150&ui=en-US&rs=en-US&ad=US

 

[kscwgirl]

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.