Forum Discussion
HELP!! Stuck on INDEX/MATCH/IF Formula for Days!
=IF(OR(ISNUMBER(SEARCH($G$4:$G$6,B4))),"Medical","")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
Sorry for some reason the picture isn't being uploaded.
here is a link for image reference: https://imgur.com/a/gvXe1K7
=IF(OR(ISNUMBER(SEARCH($G$4:$G$6,B4))),"Medical",IF(OR(ISNUMBER(SEARCH($H$4:$H$6,B4))),"Food","Other"))
I think this is because the second OR is missing in the formula. In the above formula i've added the part highlighted in red and it works in my sheet.
- Can you explain to me why we have an OR formula in this? I feel like I'm failing to understand where we apply the logical statement here. Also, tell me if I understand this correctly: SEARCH is able to partially match KFC, dunkin, raising even though the Payee description doesn't exactly match the keywords?
In order to see what the OR(ISNUMBER(SEARCH( formula does you can select the ISNUMBER(SEARCH( formula as shown in the screenshot.
Then click the F9 button to view the evaluations of the formula. In the example the results are FALSE, FALSE, TRUE (FALSCH; FALSCH; WAHR in german Excel). The result means that "raising" and "dunkin" don't appear in cell B10 "KFC payment" but "KFC" does appear. The result is FALSE, FALSE, TRUE (in this order) because "raising", "dunkin" and "KFC" are in cells H4:H6.
The OR function checks if any of the results from the ISNUMBER(SEARCH( function is TRUE.
In order to escape this view you can either press ctrl+Z or click the highlighted red sign on the left.
In order to see what the SEARCH formula returns you can select SEARCH(H4:H6;B10) in the formula bar and click F9. You can do this in the same way as with the ISNUMBER(SEARCH( formula.