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!

Hi,

Try

=VLOOKUP(C1, ni, 2,FALSE)

Mike

"SDMFG" wrote:

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!

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SDMFG" wrote:

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!