ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Lookup function in Excel (https://www.excelbanter.com/excel-worksheet-functions/13701-lookup-function-excel.html)

Feldy

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.

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

"Feldy" wrote in message
...
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.




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

--

Regards,

Peo Sjoblom

"Feldy" wrote in message
...
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.




Don S

On Thu, 17 Feb 2005 07:43:06 -0800, "Feldy"
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

Don S

On Thu, 17 Feb 2005 16:18:14 GMT, Don S
wrote:

On Thu, 17 Feb 2005 07:43:06 -0800, "Feldy"
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


All times are GMT +1. The time now is 07:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com