Forum Discussion
Two Way Lookup
=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.
- Thanks again - makes perfect sense.
The only challenge now is if the numbers across the top change ie. instead of 1-10, are then 11-21
This method relies on the numbers being considered in ROW 1.
If changing these dynamically, I guess I would have to change the formula offset each time?