Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null

[Ben]

I'm trying to compare a value in a cell to see if it is a match to a
list. Since the list is a single column, I was not certain how to do
this and attempted via the VLookup option and put my list into Column
A and in Column B...so if there is a better way, I should probably use
that.

Since I know if no other way, I used VLookup with mixed results. My
formula is as follows:

=IF(D2="","",(IF(D2<>VLOOKUP(D2,Lookup!A:B,2),"",VLOOKUP(D2,Lookup!A:B,
2))))

Here is my problem with results, and it does not make sense to me:

Where D2 = "", I receive blank (OK)
Where D2 = "Cost Group", there is no match and I receive blank (OK)
Where D2 = "Administrative", it returns "Administrative" because it is
found in Lookup Table (OK)
Where D2 = "283111 - Achievement & Development", there is no match but
it returns #N/A (Not OK)

Why for "Cost Group" does it return blank but not for "283111 -
Achievement & Development" ?? Neither values are in the lookup
table. I'm trying to avoid #N/A being returned.

Thanks!!!

 

[Pete_UK]

You would be better off using MATCH here, but to answer your main
query - there is a fourth parameter which can be used with VLOOKUP to
specify that you want an exact match (set it to FALSE or 0). If it is
set to TRUE or omitted, then Excel expects the list to be sorted and
returns a value based on the highest in the list which is less than
the lookup value. Consequently "Cost Group" does not return an error
as a match is made to the nearest (alpha) value. However, "283111 -
Achievement & Development" begins with a number and presumably all
your items in the list are text - therefore there is no lower value
than this in your list and an error (#N/A) is returned.

Keeping with your formula, you can trap this and simplify with:

=IF(ISNA(VLOOKUP(D2,Lookup!A:B,2,0)),"",VLOOKUP(D2,Lookup!A:B,2,0))

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Ben.

Pete