Index/Match return column header (multiple occurrences)

[bbr25]

Hi

I am trying to lookup a value in Column A then look across that row to find a string and return the Column header. There may be multiple occurrences of the string in that row and I need to return all column headers where there is a match. The data I am looking up is on a closed workbook.

I am able to get the column header of the first match using the following formula:

=INDEX('C:\Users\***\[workbookname.xlsm]Log'!$M$5:$FP$5,MATCH("NO",INDEX('C:\Users\***\[workbookname.xlsm]Log'!$M$6:$FP$2000,MATCH(A2,'C:\Users\***\[workbookname.xlsm]Log'!$A$6:$A$2000,0),),0))

But I am unsure how to fill that formula right to get any further matches in that row.

E.g.

Closed lookup workbook:

Column A .. Column M Column N Column O Column P Column Q .. Column FP
1 NAME 1a 2a 3a 4a 5a 160a
.
.
5 ABC NO NO
6 DEF NO NO
7 GHI NO NO NO
8 JKL NO NO
9 MNO NO NO NO
10 PQR NO NO NO



Workbook with formula:

Column A Column B Column C Column D Column E Column F
NAME Occurrence 1 Occurrence 2 Occurrence 3 Occurrence 4 Occurrence 5
1 MNO 2a
2 ABC 1a
3 PQR 1a

I can get the formula to populate Column B to get the first column header but don't know how to drag the formula across Columns C:F to return the next 4 occurrences (there may be <> 5 occurrences for each NAME). The table should then look like this:

Column A Column B Column C Column D Column E Column F
NAME Occurrence 1 Occurrence 2 Occurrence 3 Occurrence 4 Occurrence 5
1 MNO 2a 4a 5a
2 ABC 1a 4a
3 PQR 1a 3a 155a


Any help will be greatly appreciated!

 

[AhoyNC]

Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Change ranges to match your data.

Excel Workbook
	A	B	C	D	E	F	G
1	NAME	1a	2a	3a	4a	5a	160a
2	ABC	NO	NO
3	DEF	NO					NO
4	GHI	NO			NO	NO
5	JKL	NO		NO
6	MNO	NO	NO	NO
7	PQR	NO		NO		NO
8
9
10	NAME
11	ABC	1a	2a
12	DEF	1a	160a
13	GHI	1a	4a	5a
14	JKL	1a	3a
15	MNO	1a	2a	3a
16	PQR	1a	3a	5a
Sheet

 

[bbr25]

Works a treat, thanks very much!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.

 

[DrMSAbdo]

Hello there,
I have almost the same situation
with a little twist

the output list is supposed to be listed vertically (in a column), not horizontally

	A	B	C	D	E	F	G
1	NAME	1a	2a	3a	4a	5a	160a
2	ABC	NO	NO
3	DEF	NO					NO
4	GHI	NO			NO	NO
5	JKL	NO		NO
6	MNO	NO	NO	NO
7	PQR	NO		NO		NO
8
9
10	NAME
11	ABC	DEF	GHI	JKL	MNO	PQR
12	1a	1a	1a	1a	1a	1a
13	2a	160a	4a	3a	2a	3a
14			5a		3a	5a
15
16
17
18

<tbody>
</tbody>

------------------

is there any way I can do that??

 

[DrMSAbdo]

Hello there,
I have almost the same situation
with a little twist

the output list is supposed to be listed vertically (in a column), not horizontally

	A	B	C	D	E	F	G
1	NAME	1a	2a	3a	4a	5a	160a
2	ABC	NO	NO
3	DEF	NO					NO
4	GHI	NO			NO	NO
5	JKL	NO		NO
6	MNO	NO	NO	NO
7	PQR	NO		NO		NO
8
9
10	NAME
11	ABC	DEF	GHI	JKL	MNO	PQR
12	1a	1a	1a	1a	1a	1a
13	2a	160a	4a	3a	2a	3a
14			5a		3a	5a
15
16
17
18

<tbody>
</tbody>

------------------

is there any way I can do that??

Hey, I solved the problem
i found a way around it

I used your formula above, which of course worked like a charm
(MILLION thanks for that life saver )

I got the results in an intermediary worksheet in rows (just as you did above)

then I used a simple Index/Match to get the results in the final Columnar (vertical) fashion

AhoyNC , you are my Excel Hero !! at least till the next problem ... hehehehehe

Thanks again

 

[Aladin Akyurek]

@ DrMSAbdo

You can get the results you want directly if so desired....

 

[DrMSAbdo]

@ DrMSAbdo

You can get the results you want directly if so desired....

Oh please .. Do tell .....

 

[Aladin Akyurek]

Oh please .. Do tell .....

Book1
	A	B	C	D	E	F
4	GHI	NO			NO	NO
5	JKL	NO		NO
6	MNO	NO	NO	NO
7	PQR	NO		NO		NO
8
9
10	NAME
11	ABC	DEF	GHI	JKL	MNO	PQR
12	1a	1a	1a	1a	1a	1a
13	2a	160a	4a	3a	2a	3a
14			5a		3a	5a
Sheet1

In B12 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$G$1,SMALL(IF(INDEX($B$2:$G$7,MATCH(A$11,$A$2:$A$7,0),0)="no",COLUMN($B$1:$G$1)-COLUMN($B$1)+1),ROWS(A$12:A12))),"")