V lookup error

[Mahmoudelnemr]

Hello
i wonder why Vlookup shows "Value not available error", although the relevant value is available and same format.

Sheet attached, all #N/A already have corresponding values

Thanks in adbance

[Mahmoudelnemr]

And what is the "Column1" value on cell E19

[ChemistB]

You are looking for an exact match and must therefore include FALSE as a fourth argument
=VLOOKUP(D20,A:B,2,FALSE))

[Richard Buttrey]

The cells are formatted as Text. Use General or Number and you need to include the 4th 'False' parameter in the VLOOKUP

[FDibbins]

Because you did not include the last argument for vlookup - is your data sorted (1) or not (0). So excel was seaching to where it thought the match would be, if it couldnt find it in a SORTED range, it returned the cell above

=VLOOKUP(D2,A:B,2,0)
this works, but if all you want is the row number, try this, no helper needed...
=MATCH(D2,$A$2:$A$1334,0)

[Mahmoudelnemr]

Actually i want it to match the value of the first match
so if there are 3 identical values, i want to pick the first one

[Mahmoudelnemr]

the first column of the specified range is sorted, i want closest match for search_key be returned.

[FDibbins]

your formula...
=VLOOKUP(D2,A:B,2)
is simply nor working. the result...1333...is just teh last row in your range, and that contains HLXU 3684505, so it is not actually matching with anything.

either the vlookup or the match that I suggested, is doing exactly what you said you wanted.

[Mahmoudelnemr]

Many thanks, i got it

[FDibbins]

Great, happy to help