VLOOKUP can return #REF! even when the lookup value is sitting on the sheet. This one is the column index: the third argument asks for a column the range does not have. It is not the #REF! you get after you delete a cell.

Recreate it on a blank sheet

  1. Open a blank spreadsheet. In A1 type sku, B1 name, C1 price, D1 lookup, E1 result.
  2. In A2 type 101. B2 Widget. C2 9.5. D2 101. In E2 enter =VLOOKUP(D2,A2:B2,3,FALSE). The cell shows #REF!.

E2 selected. Formula bar shows =VLOOKUP(D2,A2:B2,3,FALSE). E2 is #REF! because index 3 is outside the two-column range A2:B2.

What is actually wrong

  • A2:B2 is two columns wide. Index 3 asks for a third column that range does not contain.
  • Column C is on the sheet, but it is outside the range you passed. VLOOKUP will not walk into it on its own.
  • The lookup itself can succeed. The error is the return column, not a missing key and not a deleted reference.

Fix

Leave E2 as the broken formula so the error stays on the sheet. Put each fix in a neighboring cell.

  • Want the name: change the index to 2. In F1 type fix-index. In F2 enter =VLOOKUP(D2,A2:B2,2,FALSE). The cell shows Widget.

  • Want the price: keep index 3 and widen the range. In G1 type fix-range. In G2 enter =VLOOKUP(D2,A2:C2,3,FALSE). The cell shows 9.5.

F2 selected. Formula bar shows =VLOOKUP(D2,A2:B2,2,FALSE) and F2 is Widget. E2 is still #REF!. G2 is 9.5 from =VLOOKUP(D2,A2:C2,3,FALSE).

Count the columns in the range before you pick the index. Index 1 is the lookup column. Index 3 needs a range at least three columns wide.