Vlookup is not working fails with N/A return despite i have value.
Have a look in screenshots
how can i solve it
Vlookup works with the reference search being the first column.
for your case use:
=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.
If the answer met your need, please click on the ball 
to the left of the answer, to finish the question.
read the help for VLOOKUP!!!
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
One addition:
INDEX( r_vector ; MATCH( value ; s_vector ; mode))
looking up some value in a search vector and returning something from a return vector is the most powerful and flexible solution. A “vector” is just a single row or column of cells.
This combination of 2 functions works well with horizontal and vertical vectors.
It works with ascending and descending scale lookups and in “database mode” too.
The additional functions [V / H]LOOKUP do not offer any additional functionality except some syntactical simplification.
This is an old “spreadsheet traditional”. The first LOOKUP function in the first spreadsheet Visicalc of 1979 worked in ascending scale mode. It would be less confusing if the “database mode” would have been implemented as separate functions rather than special modes of existing functions.
Awesome! Thanks for the further clarification.