Large array lookup

[hartjonathand]

You guys were super helpful on my last issue. I am trying (without defining a new function in VBA) to do a lookup where I have numbers (1-60) in multiple rows and columns (the total range is B2:AC46). In every case the name I need is next to a number so I know what I need to reference, but I don't know how to search the whole array and return whatever is adjacent to that cell. So for instance the number 40 could be in B2, so I would need C2, or it could be in Y50 so I would return Z50. Does that make sense? I would like to not use any volatile functions here as the workbook is already very computationally intensive.

 

[Marcelo Branco]

Or without a helper row (data sample above)
A4
=INDEX(B2:G7,SUMPRODUCT((B2:G7=A2)*(ROW(B2:G7)-ROW(B2)+1)),SUMPRODUCT((B2:G7=A2)*(COLUMN(B2:G7)-COLUMN(B2)+1))+1)

M.

 

[hartjonathand]

Solved! Thank you!

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[hartjonathand]

FYI for future folks I edited it slightly and made the B2 an absolute reference when pasting down a kx1 column vector.