Return column header name for item located in an array

[kgartin]

I have a list of names in one column. Those names are spread among 3 separate columns, each with a header name for each column. I need a formula that will list the header name beside each name.

FOR INSTANCE:

I have a list of names in column F1:F6
F1 = Bob
F2 = Roy
F3 = Tom
F4 = Sandy
F5 = Jill
F6 = Randy

Cell A1 is named GROUP 1 and under that header in cells A2:A4 are Bob, Roy, Tom
Cell B1 is named GROUP 2 and under that header in cells B2:B3 are Sandy, Jill
Cell C1 is named GROUP 3 and under that header in cell C2 is Randy

What formula can be written in cell G1 that can be copied down through cell G6 that will list the proper header beside each name, a-like so:

G1 = GROUP 1
G2 = GROUP 1
G3 = GROUP 1
G4 = GROUP 2
G5 = GROUP 2
G6 = GROUP 3

 

[DanteAmor]

Try

prueba carga siniestros.xlsx
	A	B	C	D	E	F	G
1	Group 1	Group 2	Group 3			Bob	Group 1
2	Bob	San	Ran			Roy	Group 1
3	Roy	Jil				Tom	Group 1
4	Tom					San	Group 2
5						Jil	Group 2
6						Ran	Group 3
Hoja1

Cell Formulas
Range	Formula
G1:G6	G1	=INDEX($A$1:$C$1,0,SUMPRODUCT(($A$2:$C$4=F1)*COLUMN($A$1:$C$1)))

 

[kgartin]

Try

prueba carga siniestros.xlsx
	A	B	C	D	E	F	G
1	Group 1	Group 2	Group 3			Bob	Group 1
2	Bob	San	Ran			Roy	Group 1
3	Roy	Jil				Tom	Group 1
4	Tom					San	Group 2
5						Jil	Group 2
6						Ran	Group 3
Hoja1

Cell Formulas
Range	Formula
G1:G6	G1	=INDEX($A$1:$C$1,0,SUMPRODUCT(($A$2:$C$4=F1)*COLUMN($A$1:$C$1)))

PERFECT! Thank you!

As a follow up, if I wanted to take the names listed in the array (A2:C4) and populate them as the list in cells F1:F6, what formula would I write in Cell F1 and copy down to F6 to make that happen?

Right now I'm using vstack (see below) but I think it (or maybe one of my indirect formulas) is causing an undisclosed issue. In addition to the indirect functions listed in my formula below, the sheet also references other sheets based on user input using other indirect functions. I have a couple of weird gremlins that I cannot figure out and that are too complicated for me to describe right now.

=VSTACK(INDIRECT("B6:B" & IF(SUMPRODUCT(MAX((B6:B50<>"")*ROW(B6:B50)))=0,6,SUMPRODUCT(MAX((B6:B50<>"")*ROW(B6:B50))))),INDIRECT("C6:C" & IF(SUMPRODUCT(MAX((C6:C50<>"")*ROW(C6:C50)))=0,6,SUMPRODUCT(MAX((C6:C50<>"")*ROW(C6:C50))))),INDIRECT("D6:D" & IF(SUMPRODUCT(MAX((D6:D50<>"")*ROW(D6:D50)))=0,6,SUMPRODUCT(MAX((D6:D50<>"")*ROW(D6:D50))))))

 

[DanteAmor]

if I wanted to take the names listed in the array (A2:C4) and populate them as the list in cells F1:F6, what formula would I write in Cell F1 and copy down to F6

The following formula gives you the data of the 2 columns F and G, and If you want to sort the results by group:

=SORT(LET(a,A2:C10,b,A1:C1,c,TEXTSPLIT(TEXTJOIN(",",TRUE,a),,","),VSTACK(c,BYROW(c,LAMBDA(br,INDEX(b,0,SUMPRODUCT((a=br)*COLUMN(b))))))),2,1,FALSE)

Ex:

Note: I assume you have excel 365.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[DanteAmor]

the sheet also references other sheets

If the names are on another sheet, for example Sheet1:

=SORT(LET(a,Sheet1!A2:C10,b,Sheet1!A1:C1,c,TEXTSPLIT(TEXTJOIN(",",TRUE,a),,","),VSTACK(c,BYROW(c,LAMBDA(br,INDEX(b,0,SUMPRODUCT((a=br)*COLUMN(b))))))),2,1,FALSE)