Find a value in a 2 dimensional array

Question

I have data stored in the range K11:AD25. Row 11 has the numbers from 1 to 20 in the columns K to AD. The cells in rows 12 to 25 each contain unique values.

Using data validation, the user can specify a value that occurs in the array and I would like to return the value in row 11 of the column that the data was found in.

           K            L            M            N            O     etc.

11       1            2             3            4             5       

12   100KC    304BC    205KH    501BH    402KC

13   102KC    305BC    207KH    505BH    406KC

etc.

For example, if the user was searching for 207KH, I want it to return 3.

Is this possible with Excel functions or does it require nested DO loops in VBA?

TIA

David

Answer 1

Hi,

Type 207KH in cell A1 and this formula in cell B1

=INDEX($K$11:$O$11,1,SUMPRODUCT(($K$12:$O$13=A1)*(COLUMN($K$11:$O$11)-COLUMN($J$11))))

Hope this helps.

Answer 2

You are welcome.  If my reply helped, please mark it as Answer.

Answer 3

Amazing Ashish! It works like a charm! I'm going through it step by step to understand how it works. Thanks so much!

David