Solved: LOOKUP, But Exact match

[sreejith]

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,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]

Try this one:

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

[Bob Phillips]

Try

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

Watch that erroneous space that the forum introduced.

[vicC]

Hello

Does this vlookup in an nested if statement help?

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

take an look at the file.

[sreejith]

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]

If you are talking about the B99 cell, you can use TRIM(B99) in the formula.

[mbarron]

Hello

Does this vlookup in an nested if statement help?

=IF(ISNA(IF(B10<>"",VLOOKUP(B10,lookuptab!C126,2,FALSE),"")),"not found",VLOOKUP(B10,lookuptab!C126,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]

Why don't you post a workbook, I am not getting a good picture of the data.

[sreejith]

Thanks everyone. The following worked well
=IF(B265>"",IF(ISNA(INDEX(Lookup!C:C,MATCH(TRIM(B265),Lookup!D,0))),"COVERAGE Not Found",INDEX(Lookup!C:C,MATCH(TRIM(B265),Lookup!D,0))),"")