VLookup with#N/A

  • Thread starter Thread starter Rui
  • Start date Start date
R

Rui

Hi friends,
I have a problem when I use VLOOKUP:
e.g. I have a lookup value range from sheet1!A1:A54, table array from
sheet2!A1:E21, Col_index_num 5, but when I return to cells G in sheet 1, only
G1:G10 return with data, the rest show as "#N/A". In fact, among these
"#N/A", there are some values can be found in sheet 2, col_index_num 5. So, I
would like to know why this happned?
 
If the problem exists when you fill the vlookup formula into other cells,
then you need to convert the data table portion of the function to an
absolute reference (Hit the F4 key after selecting that part of the
function), then fill the formula again.

The other thing to look out for is to make sure that the table is in
ascending order by the leftmost/lookup column. I usually like to make the
value that I am going to lookup in the table a list validation refering to
the lookup column to make sure that the user cannot mistype it and it will
look it up in the table properly.
 
Back
Top