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...
=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?
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?