Good morning everyone,

Quick question...

I need "NO MATCH" to appear in the cell rather than the value of the
closest LOOKUP match that satisfies the formula. How can I do this?

=LOOKUP(B25,'MO Add&Counter'!$E:$E,'MO Add&Counter'!$H:$H)
=LOOKUP(B25,'MO Add&Counter'!$E:$E,'MO Add&Counter'!$I:$I)
=LOOKUP(B25,'MO Add&Counter'!$E:$E,'MO Add&Counter'!$AN:$AN)

=LOOKUP(B25,MedicareOfc,'MO Ave Claims'!$T$2:$T$235)



FR


--
francesrivera
------------------------------------------------------------------------
francesrivera's Profile: http://www.excelforum.com/member.php...o&userid=26531
View this thread: http://www.excelforum.com/showthread...hreadid=397987

Frances,

Use VLOOKUP instead of LOOKUP. This gives you the ability to control what Excel returns to you (instead of always getting the closest value). Try the formula below:

=IF(ISERROR(VLOOKUP(B25,'MO Add&Counter'!$E:$H,4,FALSE)),"No Match",VLOOKUP(B25,'MO Add&Counter'!$E:$H,4,FALSE))

where B25 is your target value, the next argument is the full address of the table you are searching, the 4 is the column number within that table that contains the desired result, and "FALSE" tells Excel not to return the closest value, but to report an error instead. You then wrap this in an "ISERROR" function to return the desired message.

Knightly

=if(isna(LOOKUP(B25,'MO Add&Counter'!$E:$E,'MO Add&Counter'!$H:$H,FALSE),"No
Match",LOOKUP(B25,'MO Add&Counter'!$E:$E,'MO Add&Counter'!$H:$H,FALSE))
************
Anne Troy
www.OfficeArticles.com

"francesrivera"
wrote in message
news:francesrivera.1u6xaf_1124755527.4895@excelfor um-nospam.com...

Good morning everyone,

Quick question...

I need "NO MATCH" to appear in the cell rather than the value of the
closest LOOKUP match that satisfies the formula. How can I do this?

=LOOKUP(B25,'MO Add&Counter'!$E:$E,'MO Add&Counter'!$H:$H)
=LOOKUP(B25,'MO Add&Counter'!$E:$E,'MO Add&Counter'!$I:$I)
=LOOKUP(B25,'MO Add&Counter'!$E:$E,'MO Add&Counter'!$AN:$AN)

=LOOKUP(B25,MedicareOfc,'MO Ave Claims'!$T$2:$T$235)



FR


--
francesrivera
------------------------------------------------------------------------
francesrivera's Profile:
http://www.excelforum.com/member.php...o&userid=26531
View this thread: http://www.excelforum.com/showthread...hreadid=397987

Here is a sample that should help.

__ A ______ B___C______ D___
1| NO MATCH__________________
2|__________4___1_______11
3|______________3_______33
4|______________5_______55
5|______________7_______77


the formula in A1 is:
=IF(COUNTIF(C2:C5, B2) 0, VLOOKUP(B2,C2:D5,2,FALSE), "NO MATCH")


--
UofMoo
------------------------------------------------------------------------
UofMoo's Profile: http://www.excelforum.com/member.php...o&userid=26485
View this thread: http://www.excelforum.com/showthread...hreadid=397987