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!

 

[Nine Zero]

I just skimmed through this and i believe what you need is this

=if(A2="","",VLOOKUP(A2,VL_Data!A:I,9,FALSE))

 

[AhoyNC]

That was my fault I got in a hurry and gave you a bad formula.
Try this. Copy formula down as needed. If needed change ranges to match your data,

Excel Workbook
	A	B	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	Emp. Name	Supr.
2	Bart A	Mack
3	Kyle A	Mack
4	Dane B	Jerrey
VL_Data

 

[kscwgirl]

Okay so now when I copy this over and I change the A# based on my rows... it's only resulting in Mack. No other supervisor name is copying over.

 

[AhoyNC]

What is the actual range that you have for the employee name and Sup. name on sheet VL_Data?
I'm assuming on sheet 1 that employee name is starts in A2 and Sup. in cell I2.
What is the actual formula you entered that is only returning Mack?

 

[kscwgirl]

Formula is the one above that you gave me.

Cell range is as shown, currently goes through row 40 but is subject to change based on hires/terminations.

 

[AhoyNC]

By any chance did you lock both the column and the row to A2 (should be $A2 and not $A$2)?

See the example below, instead of using whole columns I made the VL_Data range $A$2:$B$100 (note this range must be locked in).

I also listed all the formulas so you can see how the formula should change as it's copied down. The employee names from one sheet to the next must match exactly.

Excel Workbook
	A	B	H	I
1	Emp. Name			Supr.
2	Acree, Bart			Mack
3	Acree, Bart			Mack
4	Acree, Bart			Mack
5
6	Axe, Kyle			Mack
7	Axe, Kyle			Mack
8
9	Borgan, Dane			Jerry
10	Borgan, Dane			Jerry
11	Borgan, Dane			Jerry
12
13	Harris, Robert			Mack
14
15	Dutton, Justin T			Nick
16	Dutton, Justin T			Nick
Sheet1

Excel Workbook
	A	B
1	Employee Name	Supervisor Name
2	Acree, Bart	Mack
3	Axe, Kyle	Mack
4	Borgan, Dane	Jerry
5	Dutton, Justin T	Nick
6	Harris, Robert	Mack
7
VL_Data

 

[kscwgirl]

Sick of me yet?

Those formulas all work, the problem now is that they are all returning a result of Mack, regardless of what the supervisor name is on the VL_Data table.. which makes NO sense to me at all.

 

[AhoyNC]

OK, do this for me.
On your sheet one copy the formula you have in cell I2 for "Acree, Brad". Then go down to "Borgan, Dane" name and copy the formula you have in column I for them and post both formulas here where I can see them. I need to see what rows each formula is referencing.

 

[kscwgirl]

Bart : =IF($A2="","",VLOOKUP($A2,VL_Data!A:B,2,FALSE))

Dane: =IF($A4="","",VLOOKUP($A4,VL_Data!$A$2:$B$103,2,FALSE))

 

[AhoyNC]

Why the different range for VL_Data (VL_Data!A:B and then VL_Data!$A$2:$B$103)?

Is Dane's name in cell A4 of your sheet 1?

Change the formula for Bart which I assume is in cell I2 to:
IF($A2="","",VLOOKUP($A2,VL_Data!$A$2:$B$103,2,FALSE))
Then copy this formula down.

Take a look at the formulas in my post #16 above this is how your formulas should be if your data starts in row 2 of sheet1.