vlookup error

[SDMFG]

100 A 100A 1000 8000
200 B 200B 2000 8000
300 C 300C 3000 8000
100-1 D 100-1D 4000 4000
100-2 E 100-2E 5000 5000
100-3 F 100-3 F 6000 6000
100-4 G 100-4 G 7000 7000
100-5 H 100-5 H 8000 8000

Ok, i have the above data. The first two columns are just text, and the
third column is a concatenate function to combine the first two columns.
Column 4 is just numbers. Also, column 3 and 4 are a named range, "ni".
Column five is a vlookup formula with the following: vlookup(C1, ni, 2).
Therefore, E1 should be 1000, but the formula is returning 8000. It seems
that the dashes or something is confusing vlookup. Any suggestions around
this error? Much appreciated!

 

[Mike H]

Hi,

Try

=VLOOKUP(C1, ni, 2,FALSE)

Mike

 

[Luke M]

You need an additional arguement in your VLOOKUP asking for an exact match.
Because your list isn't sorted (100-5 comes before 100A), its causing errors
while trying to search in a methodical manner.

=VLOOKUP(C1, ni, 2,FALSE)