VLOOUP error

  • Thread starter Thread starter John
  • Start date Start date
J

John

I continue to get Vlookup errors, even after the lists
are sorted and consistently formatted (including text to
column function applied).

If I am using a master list to lookup a value from a
subsidiary list, Excel returns an answer, even if the
value is not on the subsidiary list. Excel (in its zeal
to show how good it is?) returns the last matching lookup
value, until the next match is found.

I've struggled with this with versions of Excel back to
1997. Am I doing something wrong? I can't believe this
is a software glitch.

John
 
John,

The last argument for a vlookup (if none is specified)
is True menaing that Excel expects your table to be in
alpha/numeric order and will give you a match on the
last value that's not over the lookup value.
Setting this last argument to False will tell Excel to
find an exact match regardless of what oder the table is in.

Examples:
Consider these values in range A1 to B4
a 1
b 2
d 3
c 4

=VLOOKUP("b",A1:B4,2)
will result in "2"
(note that the last argument is left off meaing that this formula
is the same as =VLOOKUP("b",A1:B4,2, True)
=VLOOKUP("c",A1:B4,2)
will also result in an answer of 2 becuase in looking down the list,
Excel found "b", the next line was "d" so it gave you the result
from the line with the "b".
Now if you use the formula:
=VLOOKUP("c",A1:B4,2, False)
you'll get a result of "4" becuase it'll seach the table for an exact
match.
Note though that with the False argument, if an exact match is not
found,
you'll get #N/A as a result.
One way around this is to reconstruct your formula as follows:
=IF(ISNA(VLOOKUP("e",A1:B4,2, FALSE)),"",VLOOKUP("e",A1:B4,2, FALSE))

Hope this helps,
John
 
Back
Top