Does VLOOKUP compare cells

1
votes

I am currently using the VLOOKUP function for excel and I was wondering does the VLOOKUP actually compare cells or does it just use the cell straight across from it?

This is my formula "=VLOOKUP(A2,Sheet3!A2:B1814,2,FALSE)"

Sheet 1 and sheet 2 of my excel document

As you can see the VLOOKUP function works find for the first product image but it doesnt for the rest of them( it will give #N/A for most of them because the cell in the pic in the right is empty which is ok), but if you look at the picture on the left, cell A4 is the same as cell A3 on the picture on the right and it is still given me #N/A(this is because they are not in the same cells)

So my question is, is there a way for excel to check the table 1(left pic) to see is there a matching name on table2(right pic) by searching the rows?

Thanks

2
excelworksheet-function
Post the formula you are using in B4 - Dick Kusleika
i put the formula for B2 in the question there above the picture... the formula for B4 is "=VLOOKUP(A4,Sheet3!A4:B1816,2,FALSE)" - Hip Hip Array
This is not a programming question. It belongs on superuser. Voting to move. - Ken White
@Ken Its a problem with an excel formula that's why it got tagged like that. - klonq

2 Answers

3
votes

You have to use absolute references for your table array, otherwise the area will be shifted, when you copy your formula down.

=VLOOKUP(A2;Sheet3!$A$2:$B$1814;2;FALSE)
0
votes

IMG 100243 and 7216 won't work because your formula changes in each row. A really easy way to fix this is to create a named range.

  1. In the ribbon go to 'Formulas'
  2. Then 'Name Manager'
  3. Create a new named range and give it reference to Sheet3!A2:B1814
  4. Modify your formula to VLOOKUP(A2, _NAMED_RANGE_ ,2,FALSE)

That should at least resolve that problem. Let me know if that doesn't work