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.
: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))),"")
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.