seeming bug in vlookup

  • Thread starter Thread starter llorton
  • Start date Start date
L

llorton

I am trying to use vlookup to search a column of number and numbe
letter combinations and return the number in corresponding column (ie
2 returns 33)

1 45
2 33
2a 48
3 41
3b 98
4 63

etc.

When I search for the numbers alone, the answer is correct. When
search for a number letter combination, it seems always to return th
number in the previous position. i.e. a search for 3b yields 41, rathe
than 98.

I can use the old "lookup" and it works but vlookup doesn't
 
Make sure you specify an exact match in the VLOOKUP using
a 0 in the fourth argument:

=VLOOKUP(---,---,---,0)

HTH
Jason
Atlanta, GA
 
llorton,

Would help if you post your vlookup formula.

I would guess that you don't have the last argument for the vlookup
included or have it set to true (the default).

=Vlookup(what, where, index, True)
will look for the closest match that's not over the last found match.
(table must be sorted)

=Vlookup(what, where, index, False)
will look for an exact match.
(no sorting necessary)

John
 
I am trying to use vlookup to search a column of number and number
letter combinations and return the number in corresponding column (ie.
2 returns 33)

1 45
2 33
2a 48
3 41
3b 98
4 63

etc.

When I search for the numbers alone, the answer is correct. When I
search for a number letter combination, it seems always to return the
number in the previous position. i.e. a search for 3b yields 41, rather
than 98.

I can use the old "lookup" and it works but vlookup doesn't.
Click to expand...

What do you mean by 'the old "lookup"'?

Anyway, others have told you you need to use 4th argument FALSE or 0 in VLOOKUP.
That won't work. Since in your example you're searching for 3b which *appears*
to be in your table, *and* your table appears to be sorted in ascending order in
the first column (will all entries taken as text),

=VLOOKUP("3b",YourTableRange,2)

should return 98. If it returns 41, then I'd bet you have stray trailing
nonbreaking spaces in the first column of your table.

What's the result of the formula

=COUNTIF(FirstColumnOfYourTable,"3b")

? If it's zero, then you've got stray junk you need to remove. Check what it is
you need to remove with

=CODE(SUBSTITUTE(EntryInTableAppearingAs3b,"3b",""))

Does this formula return 32 or 160?
 
Back
Top