how to extract an entire column from a table in excel based on the header name that match the text in a cell outside the table

471 views excel
4

I would like excel to bring, from a specific table, all the rows of a column that has the same header name as a particular cell outside of this table. If cell A1 = Name of a header in table X, then, excel returns all rows in the column of this header.

the table is on a different sheet in the same workbook. The table has about 200 columns and 2000 rows.

enter image description here

enter image description here

answered question

Use Match() to find the column header location, then INDEX() to return the matched column.

1 Answer

2

Enter the formula with Ctrl + Shift + Enter:

{=INDEX($A$2:$C$5,ROW()-1,MATCH(1,--($A$1:$C$1=$E$1),0))}

Where:

INDEX(range,...): is the range where to return value (return range)

ROW()-1: is the row we want to return from our return range, and -1 for header.

MATCH(1,--($A$1:$C$1=$E$1),0): is the column where we can find your search value and $E$1 is the cell with the drop-down list

enter image description here

posted this

Have an answer?

JD

Please login first before posting an answer.