Lookup in two-dimensional array, return header value

[xtinct2]

Hi,

Is it possible to write a lookup function that searches a two-dimension array (rows and columns), and returns the header value for the column that the lookup was found in?
Everything I've found online references a situation where you want to return from a vertical array, but in my case, the return would be a value in a horizontal array (header row).
See attached for sample file.
Thank you

ExcelForum - xtinct2 - 2023.06.15.xlsx

[windknife]

Clean all expected data.

K5

Please Login or Register  to view this content.

[AliGW]

@Windknife - pleae explain INDEX($4:$4 ... for my edification. Thanks.

[TMS]

Try

Please Login or Register  to view this content.

Courtesy of: https://www.xelplus.com/complex-look...ex-sumproduct/

[azumi]

Put in K5 and copied down:

=IFERROR(INDEX($B$4:$H$4,SUMPRODUCT(($B$5:$H$16=J5)*COLUMN($B$5:$H$16))-COLUMN($B$4:$H$4)+1),"")

[windknife]

@Ali It don't need to use $4:$4, 4:4 is enought. I am used to adding $ in elder excel version ^_^

[wk9128]

Cell K5 formula

Please Login or Register  to view this content.

[AliGW]

@Ali It don't need to use $4:$4, 4:4 is enought. I am used to adding $ in elder excel version ^_^

That doesn't answer the querstion. What does it do? How does it work?

[HansDouwe]

@Ali, $4:$4 and 4:4 is the entire row 4. And later, the COLUMN function will select the correct cell in that row.

[TMS]

@Ali: have a look at the link in post #4 for the basics of the approach.

[windknife]

Thanks @Hans and @TMS for explaining my formula.

About my formula, first I use SUMPRODUCT((B5:H16=x)*(COLUMN(B5:H5))) to find the match column.Then, I use Index(4:4 to find the header. Of course, Index(B4:H4 is also okay.

[AliGW]

Thanks, all - the penny has now dropped (mainly thanks to Hans). I was missing the b******g obvious!!!

[Fluff13]

Another option

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Try. In K5

Please Login or Register  to view this content.

[kvsrinivasamurthy]

If there is repetition of same number below formula works. PL see file.
In K5

Please Login or Register  to view this content.