Defining a list to lookup, then matching a value

[MrBurn5]

You guys have improved my excel knowledge by leaps and bounds, but I need a little help! My wife brought me this question tonight and I feel like this would be a nested match formula but I'm failing to make it work.

For some reason "Forum Tools" isn't working nicely with Excel 2016 for Mac, so pardon the look of the attached tables.

In Sheet 1 column F, we're trying to see if the course a student took (Column C) is required for their major (Column B) depending on the lists in Sheet 2. So basically, identify which list we want to look up depending on their major, and then matching the course ID.

The formulas in F right now were just some things we were trying.

Any insight or ideas would be greatly appreciated.

[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]C[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]MAJOR[/TD]
[TD]COURSE[/TD]
[TD]GRADE[/TD]
[TD]CHRS[/TD]
[TD]REQUIRED[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

544782​

[/TD]
[TD]PNC.CT[/TD]
[TD]MAT131[/TD]
[TD]A[/TD]
[TD]

4​

[/TD]
[TD]=IF(OR(C2=Sheet2!B2, C2=Sheet2!C2, C2=Sheet2!D2, C2=Sheet2!E2), "Program", "Non-Program")[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

544782​

[/TD]
[TD]PNC.CT[/TD]
[TD]ENG101[/TD]
[TD]C[/TD]
[TD]

4​

[/TD]
[TD]=IF(OR(C3=Sheet2!B3, C3=Sheet2!C3, C3=Sheet2!D3, C3=Sheet2!E3), "Program", "Non-Program")[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

544782​

[/TD]
[TD]PNC.CT[/TD]
[TD]PSY205[/TD]
[TD]A[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

531877​

[/TD]
[TD]BSC.AAB[/TD]
[TD]BIO411[/TD]
[TD]B[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

531877​

[/TD]
[TD]BSC.AAB[/TD]
[TD]PSY511[/TD]
[TD]D[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]


Unknown 64 bit


[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH]

A​

[/TH]
[TH]B[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]MAJOR[/TD]
[TD]PROGRAM COURSE 1[/TD]
[TD]PROGRAM COURSE 2[/TD]
[TD]PROGRAM COURSE 3[/TD]
[TD]PROGRAM COURSE 4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PNC.CT[/TD]
[TD]ENG 101[/TD]
[TD]MAT131[/TD]
[TD]SOC105[/TD]
[TD]ADC208[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BSC.AAB[/TD]
[TD]BIO411[/TD]
[TD]MAT209[/TD]
[TD]SOC105[/TD]
[TD]PSYC511[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Based on the sample you provided, this will work, we'll more than likely need to Add more ranges to the formula as I'm sure there are more than just the 2 Majors to lookup.

Book1
	A	B	C	D	E	F
1	ID	MAJOR	COURSE	GRADE	CHRS	REQUIRED
2	544782	PNC.CT	MAT131	A	4	Program
3	544782	PNC.CT	ENG101	C	4	Non-Program
4	544782	PNC.CT	PSY205	A	3	Non-Program
5	531877	BSC.AAB	BIO411	B	3	Program
6	531877	BSC.AAB	PSY511	D	3	Non-Program
Sheet 1

Cell Formulas
Range	Formula
F2	=IF(COUNTIF(CHOOSE(MATCH(B2,'Sheet 2'!A$2:A$3,0),'Sheet 2'!$2:$2,'Sheet 2'!$3:$3),C2),"Program","Non-Program")

F2 formula copied down.

Book1
	A	B	C	D	E
1	MAJOR	PROGRAM COURSE 1	PROGRAM COURSE 2	PROGRAM COURSE 3	PROGRAM COURSE 4
2	PNC.CT	ENG 101	MAT131	SOC105	ADC208
3	BSC.AAB	BIO411	MAT209	SOC105	PSYC511
Sheet 2

 

[jtakw]

Before testing my formula, be sure to remove the Space within the sheet name (Sheet 2) in my formula to match yours, as you don't have a space (Sheet2) in yours.

 

[MrBurn5]

Wow, yea, that was actually easier than I thought - because I learned more new stuff. :D

I've never used CHOOSE before, and I've never seen a range defined like $2:$2

When we add in the other hundred or so majors, do we need to fix this part of the the formula: MATCH(B2,'Sheet 2'!A$2:A$3,0),'Sheet 2'!$2:$2,'Sheet 2'!$3:$3
Thusly: MATCH(B2,'Sheet 2'!A$2:A$100,0),'Sheet 2'!$2:$2,'Sheet 2'!$3:$3, 'sheet 2'!$4:$4, 'Sheet 2'!$5:$5, 'sheet 2'!$6:$6, .... and so forth?

 

[jtakw]

Yes, that's Exactly what you would do, if you run into problems, post back.

But if you have 100 or so Majors to add, the formula can get pretty lengthy, although the CHOOSE function can accommodate up to 254, if I think of another way, I'll post again.

Edit: the range $2:$2 is the Entire row 2, as I didn't know how many courses there are, that's what I used. You can replace it with something like C2:Z2, C3:Z3, etc.

2nd Edit: I noticed Sheet1 C6 is PSY511, whereas, Sheet2 E3 is PSYC511, so that's not a match, and the result is "Non-Program", might be a typo?

 

[MrBurn5]

Got it, much appreciated.

 

[jtakw]

You're welcome.

BTW, I meant B$2:Z$2, C$3:Z$3, etc. above.