SOLVED

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

Copper 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 (Copper Contributor)
Solution

@ingewashburn 

In B4:

 

=INDEX($E4:$Z4,MATCH($A$2,$E$2:$Z$2,0))&""

 

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)

@ingewashburn 

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.

1 best response

Accepted Solutions
best response confirmed by ingewashburn (Copper Contributor)
Solution

@ingewashburn 

In B4:

 

=INDEX($E4:$Z4,MATCH($A$2,$E$2:$Z$2,0))&""

 

Fill down.

View solution in original post