Forum Discussion
HELP!! Stuck on INDEX/MATCH/IF Formula for Days!
Hi there! I've been trying to create a some-what dynamic Excel worksheet for my monthly expenses, so I can analyze data in real time. Just a small intro-- I have data imported from my bank s...
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.
Dear Quadruple_Pawn,
I have one final question. Please see attached picture as reference: Excel Reference Image
As you can see, I have moments where my Amazon (and other categories also) have some transactions that have refunded back to my card. I tried adding another (IF(OR(ISNUMBER(SEARCH( formula where I made the logical statement > 0 and if it was true, it would return a "Refunds" text string.
But my formula is not working. How do I incorporate the fact that some transactions are expenses and some are refunds from the same vendor, but I want it to be in two different categories so that it doesn't sum up and give me a different net result!
=IF(AND(OR(ISNUMBER(SEARCH($G$3:$G$4,C3))),D3<0),"Refund",IF(AND(OR(ISNUMBER(SEARCH($G$3:$G$4,C3))),D3>0),"Payment",""))
You can try this formula. The AND formula checks two criteria in this case. Firstly the ISNUMBER(SEARCH( returns the TRUE or FALSE for the search strings of range G3:G4 and OR returns a single (boolean) result which is either TRUE or FALSE. Secondly D3<0 simply checks if the amount is less than 0 and returns another single (boolean) result, either TRUE or FALSE. Finally the AND function checks if the two criteria are both TRUE and returns either TRUE or FALSE.
