I have a file like the one attached.
I am trying to get the formula:
=IF(COUNTIFS(A4:F4,"*"&A2&"*",A4:F4,"*"&B2&"*")>=1,":D",":c")
To return the cell address where the statement is true (instead of the silly ":D").
For instance in that file I would want it to return the value "A4" since that is the cell where the 2 conditions of the countifs were true.
How can this be done?
Edit:
- Basically what I am trying to do is to have 2 formulas incorporated in one.
The first one will find specific text in a range so that it can do X if true and Y if false.
For this I originally tried COUNTIFS, LOOKUP and HLOOKUP, which do work more or less how I want, with HLOOKUP being the closest.
- The second one (X) would then do a MID on the cell where the first formula was true, so that I can obtain the info I want.
The problem here is that for MID I have to specify the cell where I want to use the mid on, which I cant because I need it to be dynamic (aka: the cell... any cell, where the condition was true)
I've included another excel file that better reflects this edit.
