Forum Discussion
Two Way Lookup
Hi all,
I am getting myself confused on this one - how can I return the corresponding name for a match based on the horizontal header and a non empty cell?
I know how to do a one way lookup, but struggling with this one!
Please see attached example which will explain it better!
Thank you for your help!
=IFERROR(INDEX($A$2:$A$7,SMALL(IF(ISNUMBER(OFFSET($A$2:$A$7,,$D$10)),ROW($A$2:$A$7)-1),ROW($A1))),"")
This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
Thanks, works great until I add additional columns.
Would you mind explaining the formula and how I can fix this now?
I have uploaded my example again!
Really appreciate your help!
=IFERROR(INDEX($A$3:$A$8,SMALL(IF(ISNUMBER(OFFSET($A$3:$A$8,,$E$11+1)),ROW($A$3:$A$8)-2),ROW($A1))),"")
You are welcome. The formula must be adjusted to the additional row and the additional column. Because of the additional column B the OFFSET is now $E$11+1. And because of the additonal row 2 the data is now in range $A$3:$A$8 and the formula has to look at rows ROW($A$3:$A$8)-2. The expression ROW($A$3:$A$8)-2 evaluates to {1,2,3,4,5,6} according to the 6 six rows with names.
I have added the formula in Column D.
LET(data,XLOOKUP($D$10,$B$1:$K$1,$B$2:$K$7),FILTER($A$2:$A$7,data<>0))
Hi Tejas - great method however the list always includes the first result as "0"
0 Elliott Can this be removed?
- I think the issue is with my imported dataset. Please see other thread
