No Match

F

francesrivera

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
 
U

UofMoo

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"
 
S

sirknightly

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
 
Top