excel formula for search

[ronie85]

I need a formula. Cell A19 contains 'JR', note that it will not always contain JR so I need the formula to use the contents of the cell no matter what it changes to. I am inserting a formula into cell E19 that is to search cells B3, B4, D6, D7, D8, D9, D10, D11, D12 & D13 for the text that is in A19 (on this instance JR). If the search comes back with a 'true' result then I want cell E19 (the cell with the formula) to show the word 'unavailable'. Alternatavely if the result comes back 'false' I want it to show the contents 'free')

 

[rilzniak]

There is likely a more efficient solution, but this should work:

=IF(OR(ISNUMBER(MATCH("*"&$A$19&"*",$B$3:$B$4,0)),ISNUMBER(MATCH("*"&$A$19&"*",$D$6:$D$13,0))),"unavailable","free")

This formula assumes you are looking for the letters "JR" (or whatever it changes to) anywhere in the cells within the range. So, an AJRH, JRTU, or LOJR value in the range would return "unavailable".

 

[ronie85]

Smashing!!! Thanks a lot Rilzniak, works wonders!