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
Clean all expected data.
K5
Please Login or Register to view this content.
Last edited by windknife; 06-15-2023 at 04:42 AM.
@Windknife - pleae explain INDEX($4:$4 ... for my edification. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
TryFormula:
Please Login or Register to view this content.
Courtesy of: https://www.xelplus.com/complex-look...ex-sumproduct/
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
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),"")
@Ali It don't need to use $4:$4, 4:4 is enought. I am used to adding $ in elder excel version ^_^
Cell K5 formula
Formula:Please Login or Register to view this content.
That doesn't answer the querstion. What does it do? How does it work?Originally Posted by windknife
@Ali, $4:$4 and 4:4 is the entire row 4. And later, the COLUMN function will select the correct cell in that row.
@Ali: have a look at the link in post #4 for the basics of the approach.
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.
Thanks, all - the penny has now dropped (mainly thanks to Hans). I was missing the b******g obvious!!!
Another optionFormula:Please Login or Register to view this content.
Try. In K5
Formula:Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
If there is repetition of same number below formula works. PL see file.
In K5
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks