# HOW TO: Use ADDRESS within COUNTIFS

Occasional Contributor

# HOW TO: Use ADDRESS within COUNTIFS

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.

3 Replies

# Re: HOW TO: Use ADDRESS within COUNTIFS

I don't think you want to get A4.

I think you have a XY problem.

# Re: HOW TO: Use ADDRESS within COUNTIFS

oh, I am sorry, I am editing the original post to reflect what I am actually trying :)

# Re: HOW TO: Use ADDRESS within COUNTIFS

Two files with different setups.

It is still not clearer.