PDA

View Full Version : Solved: LOOKUP, But Exact match



sreejith
02-26-2010, 06:16 AM
I am using the following to auto fill a cell in worksheet. But it returns a nearest value if it cannot find an exact match

=IF(B99>"",LOOKUP(B99,Lookuptab!D:D,Lookuptab!C:C),"")

Is there any other way so that I can auto fill a "NOT FOUND" if an exact match is not found.

In short I don't want the following feature of lookup

If LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

Thanks
Sreejith

mbarron
02-26-2010, 07:18 AM
Try this one:

=IF(B99>"",IF(ISNA(INDEX(Lookuptab!C:C,MATCH(B99,Lookuptab!D:D,0))),"Not Found",INDEX(Lookuptab!C:C,MATCH(B99,Lookuptab!D:D,0))),"")

Bob Phillips
02-26-2010, 07:25 AM
Try

=IF(B99>"",,IF(ISNUMBER(MATCH(B9,LookupTab!D:D,0)),INDEX(LookupTab!C:C,MATCH(B9,Looku pTab!D:D,0)),"Not Found"),"")

Watch that erroneous space that the forum introduced.

vicC
02-26-2010, 07:49 AM
:hi:Hello

Does this vlookup in an nested if statement help?

=IF(ISNA(IF(B10<>"",VLOOKUP(B10,lookuptab!C1:D26,2,FALSE),"")),"not found",VLOOKUP(B10,lookuptab!C1:D26,2,FALSE))

take an look at the file.

sreejith
02-26-2010, 08:03 AM
mbarron/xld,
Thanks very much. It worked. One minor problem with my spreadsheet is some of them got trailing spaces at the end in column B and hence showing up notfound. I use the space trimmer from ablebits . com. I am wondering if there is another solution.

Thanks
Sreejith

mbarron
02-26-2010, 08:51 AM
If you are talking about the B99 cell, you can use TRIM(B99) in the formula.

mbarron
02-26-2010, 08:55 AM
:hi:Hello

Does this vlookup in an nested if statement help?

=IF(ISNA(IF(B10<>"",VLOOKUP(B10,lookuptab!C1:D26,2,FALSE),"")),"not found",VLOOKUP(B10,lookuptab!C1:D26,2,FALSE))

take an look at the file.

The OP is looking for a value in the D column and returning a value from the C column. VLOOKUP would not work in this situation.

Bob Phillips
02-26-2010, 09:51 AM
Why don't you post a workbook, I am not getting a good picture of the data.

sreejith
02-26-2010, 01:29 PM
Thanks everyone. The following worked well
=IF(B265>"",IF(ISNA(INDEX(Lookup!C:C,MATCH(TRIM(B265),Lookup!D:D,0))),"COVERAGE Not Found",INDEX(Lookup!C:C,MATCH(TRIM(B265),Lookup!D:D,0))),"")