Excel Formula to Find Value and Return Column – How to Guide

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

Related Question