Index & match across multi coumns I think?

[NGB82KS]

Hi all, im not sure the best way to get the following:
I have 2 tabs with tables (like below) and im trying to get a formula in column DI that looks at table1, column BK and then BQ, BS & BU and if their is a match (e.g. MATH 100; MATH 105; MATH 107) to table2's column A&B then returns a "Match" in column DI, otherwise its a "NO MATCH". I assume its a variation on Index & Match?

BK (CLASS)	BQ	BS	BU	DI
MATH	100	105	107	MATCH
MATH	102	103	104	NO MATCH

(SHEET 2 - TABLE1)

A (CLASS)	B (CODE)
MATH	100
SCIENCE	101
HISTORY	102
ENGLISH	103

(SHEET 3 - TABLE2)

 

[DRSteele]

Let's try this:

MrExcel posts19.xlsx
	A	B	C	D	E
1	BK (CLASS)	BQ	BS	BU	DI
2	MATH	100	105	107	Match
3	MATH	102	103	104	No Match
4	SCIENCE	101		104	Match
5	ENGLISH	101	102	103	Match
6
7
8	A (CLASS)	B (CODE)
9	MATH	100
10	SCIENCE	101
11	HISTORY	102
12	ENGLISH	103
Sheet24

Cell Formulas
Range	Formula
E2:E5	E2	=IF(SUM(--(A2&B2:D2=$A$9:$A$12&$B$9:$B$12)),"Match","No Match")

 

[NGB82KS]

Let's try this:

MrExcel posts19.xlsx
	A	B	C	D	E
1	BK (CLASS)	BQ	BS	BU	DI
2	MATH	100	105	107	Match
3	MATH	102	103	104	No Match
4	SCIENCE	101		104	Match
5	ENGLISH	101	102	103	Match
6
7
8	A (CLASS)	B (CODE)
9	MATH	100
10	SCIENCE	101
11	HISTORY	102
12	ENGLISH	103
Sheet24

Cell Formulas
Range	Formula
E2:E5	E2	=IF(SUM(--(A2&B2:D2=$A$9:$A$12&$B$9:$B$12)),"Match","No Match")

Hmmm i tried that but using my tables and that didn't work, the table I'm referencing is on the 3rd tab not sure if that matters because this code is for everything on one sheet.

 

[NGB82KS]

Let's try this:

MrExcel posts19.xlsx
	A	B	C	D	E
1	BK (CLASS)	BQ	BS	BU	DI
2	MATH	100	105	107	Match
3	MATH	102	103	104	No Match
4	SCIENCE	101		104	Match
5	ENGLISH	101	102	103	Match
6
7
8	A (CLASS)	B (CODE)
9	MATH	100
10	SCIENCE	101
11	HISTORY	102
12	ENGLISH	103
Sheet24

Cell Formulas
Range	Formula
E2:E5	E2	=IF(SUM(--(A2&B2:D2=$A$9:$A$12&$B$9:$B$12)),"Match","No Match")

Nevermind, i forgot to make it an array with ctrl+shift+enter! Awesome

 

[DRSteele]

My apologies. I should have remembered that.
But of course the need for CTRL SHIFT Enter went extinct with the dodo-bird and 8-track tapes after Excel 365 came along.