In B4:
=INDEX($E4:$Z4,MATCH($A$2,$E$2:$Z$2,0))&""
Fill down.
Jul 11 2022 07:08 AM - edited Jul 11 2022 07:11 AM
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?
Jul 11 2022 07:25 AM
SolutionIn B4:
=INDEX($E4:$Z4,MATCH($A$2,$E$2:$Z$2,0))&""
Fill down.
Jul 11 2022 07:30 AM - edited Jul 11 2022 07:33 AM
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)
Jul 11 2022 07:59 AM
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.
Jul 11 2022 07:25 AM
SolutionIn B4:
=INDEX($E4:$Z4,MATCH($A$2,$E$2:$Z$2,0))&""
Fill down.