2 way lookups, return column header, but multiple matches in row

[kizzie37]

I have a large database on the "Colated" sheet in my excel workbook:

Column E Contains Course Names
Row F1 - HZ1 Contains Role Title
The body contains either Required, Not Required or Variable

Course Name is entered into C3
H4 contains either Required, Not Required or Variable

I want to use a forumla such as the one I am using below, to return ALL the column headers applicable, e.g if the course name is in row E1 and there are 3 instances where "Required" applies, I want the 3 column headers (Role title) to be returned


The below formula will only return one match, (it is the last match in the row),
=INDEX(Colated!$F$1:$HZ$1,MATCH($H$4,INDEX(Colated!$F$2:$HZ$350,MATCH($C$3,Colated!$E$2:$E$350),)))

Can anyone advise, I think it needs an array, but im not sure how to get the multiple match returns.

thanks

 

[Andrew Poulsom]

Try in I4:

=IFERROR(INDEX(Colated!$F$1:$HZ$1,SMALL(IF((Colated!$E$2:$E$350=$C$3)*(Colated!$F$2:$HZ$350=$H$4),COLUMN(Colated!$F$1:$HZ$1)-COLUMN(Colated!$F$1)+1),COLUMNS($I4:I4))),"")

confirmed with Ctrl+Shift+Enter and copied across.

 

[kizzie37]

I tried this, but it comes up with a blank response, also not sure I understand the relevance of I4?. Maybe Im doing something wrong

 

[Andrew Poulsom]

I tested with this sample data:

Excel 2010
	E	F	G	H	I	J	K	L	M	N	O
1	Course	Role1	Role2	Role3	Role4	Role5	Role6	Role7	Role8	Role9	Role10
2	Course1
3	Course2		Required						Required
4	Course3
5	Course1
6	Course2					Required
7	Course3
Colated

Excel 2010
	C	D	E	F	G	H	I	J	K	L
3	Course2
4						Required	Role2	Role5	Role8
Sheet1

Cell Formulas
Range	Formula
I4	{=IFERROR(INDEX(Colated!$F$1:$HZ$1,SMALL(IF((Colated!$E$2:$E$350=$C$3)*(Colated!$F$2:$HZ$350=$H$4),COLUMN(Colated!$F$1:$HZ$1)-COLUMN(Colated!$F$1)+1),COLUMNS($I4:I4))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kizzie37]

For some reason its returning an error for me, in both the test sheet and my sheet, hence the blank

 

[kizzie37]

my fault, it now works in the sample sheet, just need to figure out the issue in the main sheet. Thanks for the help

 

[kizzie37]

Andrew, I finally figured out what my issue was, but I wondered, if I wanted to change it around, i.e. look up by role, then retunr the courses, what would I need to change?

 

[Andrew Poulsom]

Maybe you can adapt this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Course	Role1	Role2	Role3	Role4	Role5	Role6	Role7	Role8	Role9	Role10	Required	Course1	Role1	Role4	Role5
2	Course1	Required											Course2	Role2	Role5	Role8	Role10
3	Course2		Required						Required				Course3	Role3	Role5	Role7	Role8	Role9
4	Course3			Required		Required
5	Course1				Required	Required								Role1	Role2	Role3	Role4	Role5
6	Course2					Required					Required			Course1	Course2	Course3	Course1	Course3
7	Course3							Required	Required	Required								Course1
8																		Course2
Sheet1

Cell Formulas
Range	Formula
N1	{=IFERROR(INDEX($B$1:$K$1,SMALL(IF(($A$2:$A$7=$M1)*($B$2:$K$7=$L$1),COLUMN($B$1:$K$1)-COLUMN($B$1)+1),COLUMNS($N1:N1))),"")}
N6	{=IFERROR(INDEX($A$2:$A$7,SMALL(IF(($B$1:$K$1=N$5)*($B$2:$K$7=$L$1),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(N$6:N6))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kizzie37]

Perfect, thanks very much!

 

[kizzie37]

If I wanted to amend this portion to be a range rather than an absolute, would that be possible

$B$2:$K$7=$L$1 (i.e. L1:L14)