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

1. ## Re: Broken VLookup

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

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

2. ## Re: Broken VLookup

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,

Sheet1

 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

 Cell Formula I2 =IF(\$A2="","",VLOOKUP(\$A2,VL_Data!A:B,2,FALSE))

VL_Data

 A B 1 Emp. Name Supr. 2 Bart A Mack 3 Kyle A Mack 4 Dane B Jerrey

Excel tables to the web >> Excel Jeanie HTML 4

3. ## Re: Broken VLookup

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.

4. ## Re: Broken VLookup

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?

5. ## Re: Broken VLookup

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.

6. ## Re: Broken VLookup

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.

Sheet1

 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

 Cell Formula I2 =IF(\$A2="","",VLOOKUP(\$A2,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I3 =IF(\$A3="","",VLOOKUP(\$A3,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I4 =IF(\$A4="","",VLOOKUP(\$A4,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I5 =IF(\$A5="","",VLOOKUP(\$A5,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I6 =IF(\$A6="","",VLOOKUP(\$A6,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I7 =IF(\$A7="","",VLOOKUP(\$A7,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I8 =IF(\$A8="","",VLOOKUP(\$A8,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I9 =IF(\$A9="","",VLOOKUP(\$A9,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I10 =IF(\$A10="","",VLOOKUP(\$A10,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I11 =IF(\$A11="","",VLOOKUP(\$A11,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I12 =IF(\$A12="","",VLOOKUP(\$A12,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I13 =IF(\$A13="","",VLOOKUP(\$A13,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I14 =IF(\$A14="","",VLOOKUP(\$A14,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I15 =IF(\$A15="","",VLOOKUP(\$A15,VL_Data!\$A\$2:\$B\$100,2,FALSE)) I16 =IF(\$A16="","",VLOOKUP(\$A16,VL_Data!\$A\$2:\$B\$100,2,FALSE))

VL_Data

 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

Excel tables to the web >> Excel Jeanie HTML 4

7. ## Re: Broken VLookup

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.

8. ## Re: Broken VLookup

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.

9. ## Re: Broken VLookup

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

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

10. ## Re: Broken VLookup

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.