Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to make cells in one sheet look up a name in another sheet, and return the value one column to the right of the cell with the name. In itself, a perfect job for VLOOKUP. But, when I implement this in practice, with the formula =VLOOKUP(B2;TypeID!$A$2:$A$8999;2;FALSE), the cell displays "#REF", describing it as "Invalid Cell Reference Error".

I have tried substituting VLOOKUP with an INDEX+MATCH combination, but with the exact same results. I have manually assured that the values in both the lookup_value cell and the table_array cells are the same, with =B2=TypeID!A592, and that they are of the same type, with TYPE. When I use VLOOKUP with index at 1, it returns the value of the cell correctly.

It might be worth noting that the lookup_value and table_array cells are text, but the value I want returned, from the col_index_num cell, is a number.

A small exerpt from the TypeID sheet:

A                     B
1 Amber Cytoserocin  |  ISK 89 999,97 
2 Amber Mykoserocin  |  ISK 2 500,00 

I want to look up a match in the A column, then extract the neighboring value from the B column.

share|improve this question

migrated from Jun 21 '13 at 16:46

This question came from our site for professional and enthusiast programmers.

See answer below. You are only using the Column A range is your VLOOKUP. You need to include Column B – Abe Gold Jun 21 '13 at 12:54

Your range is incorrect. Try this:

share|improve this answer
Didn't work. Gives me "#N/A", Value Not Available, instead. – Gipphe Jun 23 '13 at 8:56

=vlookup(B2;TypeID!A:B,2;FALSE) You don't want to use $ on v lookup you want a relative reference not an absolute one. Selecting columns is better if your data changes you don't have to go back and re-do the formulas. If you are new to V-lookup it is helpful to use the formula box and click on the cell you want to reference and the range to make sure you are selecting the right thing the number of the column is more tricky but you can use display column number to see what it is.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.