HLOOKUP formula to return all the values in that column

[SPA812]

I need a formula that can find a column header in a table, then return all the values in that column. So if I have a column of text data, it will identify that column based on the header, then return a list of the text values. Sort of like an HLOOKUP, that not only returns the next row value, but the entire column (list)

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	Header1	Header2	Header3	------	Header2
2	1	6	11		6
3	2	7	12		7
4	3	8	13		8
5	4	9	14		9
6	5	10	15		10
7

This formula entered in E2:

=IFERROR(INDEX(A$2:C$6,ROWS(E$2:E2),MATCH(E$1,A$1:C$1,0)),"")

Copy down until you get blanks.

[ChemistB]

or (just for variety)

With the header you want to find in M1 and using Tony's table
=HLOOKUP($M$1, $A$1:$E$6, ROWS($A$1:$A2), FALSE)
copied down

[SPA812]

Great! This is working well (Tony's). Any way to adjust it so that it returns a list of unique values (removing duplicates)

[Tony Valko]

Like this...

Data Range

	A	B	C	D	E
1	Header1	Header2	Header3	------	Header2
2	1	6	9		6
3	2	6	10		7
4	3	7	11		8
5	4	8	12
6	5	8	13
7

This array formula** entered in E2:

=IFERROR(INDEX(A$2:C$6,MATCH(0,COUNTIF(E$1:E1,INDEX(A$2:C$6,0,MATCH(E$1,A$1:C$1,0))),0),MATCH(E$1,A$1:C$1,0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[nwatson1]

Hi, Appreciate this is an old thread...I have a similar request - but instead of returning unique values, ignore any blank cells in the column and return a continuous list

Attachment 706950

[Glenn Kennedy]

1. Please read rule 4: "Do not post a new help request in an existing thread."

2. Start your OWN thread.

3. Your file did not attach. Read the yellow banner at the top of the page and try again to attach a small sample sheet in your NEW thread, with some manually calculated expecte answers in the place where you expect to see them.