Iserror Match if one of two cells includes text

[rsmeyerson]

I am trying to return a value if one of two cells includes text by using "IF(ISERROR(MATCH" formula. I've added the OR function to try to do this but a value is not being returned even if one of the two cells includes text.

Here is my formula:

=IF(ISERROR(OR(MATCH(B172,'[SheetName.xlsm]Sheet1'!A:A, 0),MATCH(B171,'[SheetName.xlsm]Sheet1'!A:A, 0))), "", "Adj New:")

Thank you for your help.

 

[mikerickson]

Try
=IF(OR(ISTEXT(B172),ISTEXT(B171)), "one or more has text", "neither")

 

[rsmeyerson]

Thank you. Is there a way to include the MATCH function with this?

 

[mikerickson]

Why use MATCH, the question is "do either of the cells contain text". MATCH is not needed.

 

[Rick Rothstein]

Thank you. Is there a way to include the MATCH function with this?

Why are you trying to force a MATCH function solution when Mike has given you a simple, straightforward formula that appears to do exactly what you posted you wanted? As a matter-of-fact, if the possibilities for the cell are having text or being blank (as opposed to having a numeric value), then there is an even simpler formula available...

=IF(LEN(B171&B172),"Has text", "one or more has text", "neither")

 

[rsmeyerson]

I’m sorry. My question wasn’t clear. My original formula worked when matching B171 only. I’m trying to MATCH B171 OR B172. I added the OR function but the formula is not working. Can this match be added to with of the solutions provided here? Thank you for your help and time.

 

[Aladin Akyurek]

I’m sorry. My question wasn’t clear. My original formula worked when matching B171 only. I’m trying to MATCH B171 OR B172. I added the OR function but the formula is not working. Can this match be added to with of the solutions provided here? Thank you for your help and time.

Try…

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(B171:B172,'[SheetName.xlsm]Sheet1'!A:A, 0))),"","Adj New:")

 

[mikerickson]

I’m trying to MATCH B171 OR B172. ....

With what are you trying to match B171 or B172?
What does it mean to "match with"?
Can you give an some examples of inputs and the desired results?

 

[Rick Rothstein]

Does this formula do what you want...

=IF(COUNTIF([SheetName.xlsm]Sheet1!A:A,B171)+COUNTIF([SheetName.xlsm]Sheet1!A:A,B172),"Adj New:","")

Edit Note: You might want to consider Aladin's formula instead of mine as his formulas tend to be the most efficient.

 

[rsmeyerson]

Aladin's formula worked. I only had to reverse <"Adj New:",""> at the end of the formula for proper true/false result. Thank you very much to all who replied. I appreciate your help.