autopopulating table (VLOOKUP / HLOOKUP / XLOOKUP / INDEX(MATCH))

Occasional Contributor

I am trying to create an auto-populating table based on a different table.


Screenshot (7).png


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?

3 Replies
best response confirmed by ingewashburn (Occasional Contributor)


In B4:




Fill down.

@Hans Vogelaar


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.