I have some data organized similar to the below:
Week 1 Week 2 Week 3
HCE738886 HCE737035 HCE737742
HCE736755 HCE738587 HCE737823
HCE738711 HCE737139 HCE736870
The data would start in Cell A1.
I want to be able to find out which column the value HCE737139 appears in.
In my actual data set there are 100s of columns and 1000s of Rows, and I will need to find more than one value.
Is there a formula that can do this, something like a 2 step MATCH ?
I have tried using MATCH but can only make this work to find the exact position within a single column or row.
Ideally I would like to avoid any macros for this, as the look up values may at time be on a different sheet, and sometime different workbook, so any UDFs may not work in these instances.
I'm hoping some type of array can do the trick.
Considering your range is A1:C3:
For the complete address of the cell, you can use =ADDRESS(SUMPRODUCT(--(A1:C3="HCE737139")*ROW(A1:C3)),SUMPRODUCT(--(A1:C3="HCE737139")*COLUMN(A1:C3)))
For only the column number, SUMPRODUCT(--(A1:C3="HCE737139")*COLUMN(A1:C3))
For the actual column letter, =SUBSTITUTE(ADDRESS(1,SUMPRODUCT(--(A1:C3="HCE737139")*COLUMN(A1:C3)),4),1,)
While SUMPRODUCThere is working as an array, this is not an array formula, so no need for CTRL+ENTER
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments