EXCEL Formula to find a value and return column it is found in

microsoft excel

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.

Best Answer

  • 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