SOLVED

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

Occasional Contributor

# 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?

3 Replies
best response confirmed by ingewashburn (Occasional Contributor)
Solution

# Re: autopopulating table (VLOOKUP / HLOOKUP / XLOOKUP / INDEX(MATCH))

In B4:

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

Fill down.

# Re: autopopulating table (VLOOKUP / HLOOKUP / XLOOKUP / INDEX(MATCH))

@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)

# Re: autopopulating table (VLOOKUP / HLOOKUP / XLOOKUP / INDEX(MATCH))

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.