Thread: Lookup function in Excel

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.

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

"Feldy" <Feldy@discussions.microsoft.com> wrote in message
news:C9B8DB10-AE9C-4B34-BC04-EBAC473ECD23@microsoft.com...
> 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.

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

--

Regards,

Peo Sjoblom

"Feldy" <Feldy@discussions.microsoft.com> wrote in message
news:C9B8DB10-AE9C-4B34-BC04-EBAC473ECD23@microsoft.com...
> 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.

On Thu, 17 Feb 2005 07:43:06 -0800, "Feldy"
<Feldy@discussions.microsoft.com> wrote:

>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

On Thu, 17 Feb 2005 16:18:14 GMT, Don S
<dontspamregistrations@swbell.net> wrote:

>On Thu, 17 Feb 2005 07:43:06 -0800, "Feldy"
><Feldy@discussions.microsoft.com> wrote:
>
>>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

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

Don S