How can I get LOOKUP to return a 'blank' value when a term is not found in
the Lookup arrray? At present, what is being returned is the next
alphabetical entry in the Lookup array.

Details. I have a worksheet 'Exhibitors' showing around 1000 Company Names
of people exhibiting at a trade show. I also have a worksheet 'Contacts' that
show the Company Names and contact names of some 50 people that we already
know in that trade. I want to populate 'Exhibitors' with a column of existing
contacts. I thought I'd use LOOKUP in Exhibitors to look up Company Name in
the Contacts array and return the contact name.

But obviously there are many Exhibitors for whom we have no contact yet, and
Lookup is returning the next alphabetical name. (If I look up Heart, and
there is no Heart, I'll get Heath as the next alphabetical name).

How can I have Excel return the name I am looking for, and show 'None' if we
have no contact?

Thanks.

Mike

Hi Mike,

Set the 4th argument of the Vlookup formula to FALSE. That will reurn #NA if
the item is not in the list.
To suppress that:
=IF(ISNA(YourFormula),"None",YourFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"MichaelRobert" wrote in message
...
How can I get LOOKUP to return a 'blank' value when a term is not found in
the Lookup arrray? At present, what is being returned is the next
alphabetical entry in the Lookup array.

Details. I have a worksheet 'Exhibitors' showing around 1000 Company Names
of people exhibiting at a trade show. I also have a worksheet 'Contacts'
that
show the Company Names and contact names of some 50 people that we already
know in that trade. I want to populate 'Exhibitors' with a column of
existing
contacts. I thought I'd use LOOKUP in Exhibitors to look up Company Name
in
the Contacts array and return the contact name.

But obviously there are many Exhibitors for whom we have no contact yet,
and
Lookup is returning the next alphabetical name. (If I look up Heart, and
there is no Heart, I'll get Heath as the next alphabetical name).

How can I have Excel return the name I am looking for, and show 'None' if
we
have no contact?

Thanks.

Mike