# Vlookup is not working fails with N/A return despite i have value

Vlookup is not working fails with N/A return despite i have value.
Have a look in screenshots
how can i solve it

Read VLOOKUP documentation carefully, especially its first and last arguments.

Vlookup works with the reference search being the first column.

``````=INDEX(\$B\$6:\$C\$16;MATCH(J12;\$C\$6:\$C\$16;0);1)
``````

In this way the search can be to the right or left of the search key, to change the column number.

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

Your SearchRange isnt sorted on Column F … so you will need 4th parameter in VLOOKUP:

=VLOOKUP(B2,\$F\$2:\$G\$10035,2,0)

1 Like

Thank you! It worked. I did indeed read the VLOOKUP help but do not fully understand it. I do not fully understand what difference the Zero after the comma at the end did. Thanks again for saving my day!

LOOKUP, MATCH and V/HLOOKUP are typical spreadsheet functions. However, most people try to mis-use a spreadsheet program as a database.
1 A
3 B
5 C
7 D
=LOOKUP( 4 ; A1:B4) => B although 4 does not occur in the search column.
Same result with equivalent VLOOKUP( 4 ; A1:B4 ; 2) or VLOOKUP( 4 ; A1:B4 ; 2 ; 1) or INDEX(B1:B4 ; MATCH( 4 ; A1:A4)). Spreadsheet lookups scan an ascending numeric scale and return some corresponding value that falls into a certain range of that scale. The functions match at the last position where the lookup vector (1,3,5,7) is equal or smaller than the search value(4). An unsorted search vector may match too early at some position although there would be closer matches further below. Switch rows 2 and 3 in the above example and the lookup matches at 1 returning “A” although “B” at 3 would match better.

LOOKUP only works in spreadsheet mode.
V/HLOOKUP and MATCH run in database mode when the last argument is explicitly 0. In database mode these functions match the first occurrance of an exactly matching value anywhere in the lookup vector. Sort order does not matter.
MATCH has an additional mode -1 for descending scales. In this mode it matches at the last position where the lookup vector is equal or bigger than the search value.

2 Likes

``````INDEX( r_vector ; MATCH( value ; s_vector ; mode))