Lookup function in Excel

[Feldy]

When using the lookup function in Excel 2003, it would be extremely helpful
to have an error appear if the looked up item does not appear in the list.
Example: If I have a small list of students in list A and I'm trying to look
up the corresponding teacher in list B, it returns a teacher for every
student in list A, even if that student is not in list B, and therefore, it
is the wrong information. I would rather have it display an error message,
so that I know that something is missing.

 

[JulieD]

Hi

sounds like you need to use the VLOOKUP function with the fourth parameter
set to 0 or FALSE, if you omit the fourth parameter it defaults to TRUE or
in other words - an "approximate match" instead of an "exact match." (which
is what the 0 or False will give you). In this case if the item is not
found a #NA error will be returned.

Cheers
JulieD

 

[Peo Sjoblom]

What lookup function are you using? If you use VLOOKUP you can use FALSE or
0 to find an exact match

=VLOOKUP(A1,B2:C400,2,0)

will return #N/A if lookup is not found

 

[Don S]

When using the lookup function in Excel 2003, it would be extremely helpful
to have an error appear if the looked up item does not appear in the list.
Example: If I have a small list of students in list A and I'm trying to look
up the corresponding teacher in list B, it returns a teacher for every
student in list A, even if that student is not in list B, and therefore, it
is the wrong information. I would rather have it display an error message,
so that I know that something is missing.

If you want an error message in the lookup formula, add an option
after the number of columns. IE: =3Dvlookup(cellref,lookup
range,columns,FALSE). If the cellref isn't in the lookup range, the
results of the formula will be '#N/A".

Hope that is what you wanted.

Don S

 

[Don S]

If you want an error message in the lookup formula, add an option
after the number of columns. IE: =3Dvlookup(cellref,lookup
range,columns,FALSE). If the cellref isn't in the lookup range, the
results of the formula will be '#N/A".

Hope that is what you wanted.

Don S

Sorry, I don't know where the "3D" in my vlookup came from. Leave it
out.

Don S