In B4:
=INDEX($E4:$Z4,MATCH($A$2,$E$2:$Z$2,0))&""
Fill down.
Forum Discussion
Solved
autopopulating table (VLOOKUP / HLOOKUP / XLOOKUP / INDEX(MATCH))
I am trying to create an auto-populating table based on a different table.
The yellow cell is a dropdown menu based on the healthcare plan names (Data > Data Validation > Source: =$E$2:$Z$2). I want to autopopulate the left table with information from the right table based on the selected healthcare plan ($A$2). My current formula is: =INDEX($E$2:$Z$26,MATCH($A$2,$E$2:$Z$26)). I was able to create the autopopulating table by transposing the data but want to keep the table to the right in its current format.
What do you recommend doing?
In B4:
=INDEX($E4:$Z4,MATCH($A$2,$E$2:$Z$2,0))&""
Fill down.
Thank you so much, Hans! Could you please explain what I was doing wrong in the original formula? (I'm newer to excel and want to learn)
MATCH can be used to look up a value in a single row or a single column, but not in a range with multiple rows and columns. So MATCH($A$2,$E$2:$Z$26) won't work: it will return #N/A.
The formula that I posted looks up the program selected in A2 in the range E2:Z2, a single row.
