CODEWITHSUNDEEP
excelexcel-formulaarray-formulas
RugsKid
RugsKid

Reputation: 344

Formula to Retrieve Multiple Column Numbers in Which a Value Appears

I am trying to use an on sheet formula that will provide me with all the column numbers in which a value exists. For the sake of example: I want to find all the columns on Sheet1 that have a value of ThisHeader in Row1.

I have been able to use the below formula to retrieve the result I want if the value I'm searching for only appears one time:

=MATCH("ThisHeader",1:1,0)

I'm unsure how to implement this same logic, but give me multiple column numbers if ThisHeader exists in multiple columns.

I'm not particular about how the result is displayed, although ideally I'd use something like: =SUBSTITUTE(ADDRESS(1,col_number,4),"1","") after the column numbers are retrieved in order to translate to a letter format. perhaps with a comma or dash separating each number/column letter. I could add or use multiple formulas and columns rather than a nested formula as well if that is the best or only route.

Thanks in advance!

Upvotes: 0

Views: 325

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If you have O365, you can use:

=AGGREGATE(15,6,1/($1:$1="ThisHeader")*COLUMN($1:$1),SEQUENCE(COUNTIF($1:$1,"ThisHeader")))

If you do not have the SEQUENCE function, you can replace it and use:

=AGGREGATE(15,6,1/($1:$1="ThisHeader")*COLUMN($1:$1),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTIF($1:$1,"ThisHeader"))))

enter image description here

Results

enter image description here

The formula returns an array of the column numbers. So, to visualize them if you don't have the dynamic array feature of recent Excel versions, you may have to enter this as an array formula (with ctrl+shift+enter over multiple cells. Or by using an INDEX function to return each element.

Upvotes: 1

Related Questions