Match/Index Function with AND/OR

[dman78]

want an output in G2 that inputs "Peter / Scheduled" based on whether the time in Column C is "Full Day" or "AM" based on an index/match that uses an OR variable.

So if Peter is scheduled for AM or FULL DAY, I want it to be shown in G2 and if he is scheduled for the afternoon I want G4 to be populated.

I have the formula for G4, nut not for G2 where it involves an OR Match. I get an #NA error. Also Each Date has ONLY three options: AM, PM, FULLDAY.

The forumula for G4 {=INDEX(A2:D6,MATCH(1,(A2:A6="PETER")*(B2:B6=H1)*(C2:C6="PM"),0),4)} which WORKS!

I just need to know how to complete G2

Thank you Kindly!

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Staff	Date	Time	Result		Peter Time	April 14	April 15
2​	Mike	April 1	AM	Mike / Scheduled		AM or Full Day	#NA	#NA
3​	Sally	April 14	PM	Sally / Scheduled
4​	Peter	April 14	PM	Peter / Scheduled		PM	Peter / Scheduled	Peter / Scheduled
5​	Peter	April 14	Full Day	Peter / Scheduled
6​	Peter	April 15	PM	Peter / Scheduled

 

[Fluff]

What version of Excel are you using?

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’)

 

[dman78]

What version of Excel are you using?

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’)

Done!

 

[dman78]

Done!

What version of Excel are you using?

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’)

Office 2016

 

[Fluff]

Thanks for that.
How about

=INDEX(A2:D6,MATCH(1,(A2:A6="PETER")*(B2:B6=H1)*((C2:C6="AM")+(C2:C6="Full day")),0),4)

 

[kweaver]

The "and" condition you found works with multiply.
An "or" condition works with addition.

Book1
	A	B	C	D	E	F	G	H
1	Staff	Date	Time	Result		Peter Time	14-Apr	15-Apr
2	Mike	1-Apr	AM	Mike / Scheduled		AM or Full Day	Peter / Scheduled	#N/A
3	Sally	14-Apr	PM	Sally / Scheduled
4	Peter	14-Apr	PM	Peter / Scheduled		PM	Peter / Scheduled	Peter / Scheduled
5	Peter	14-Apr	Full Day	Peter / Scheduled
6	Peter	15-Apr	PM	Peter / Scheduled
Sheet1

Cell Formulas
Range	Formula
G2:H2	G2	=INDEX($A$2:$D$6,MATCH(1,($A$2:$A$6="PETER")*($B$2:$B$6=G1)*(($C$2:$C$6="AM")+($C$2:$C$6="Full Day")),0),4)
G4:H4	G4	=INDEX($A$2:$D$6,MATCH(1,($A$2:$A$6="PETER")*($B$2:$B$6=G1)*($C$2:$C$6="PM"),0),4)

 

[dman78]

Thanks for that.
How about

=INDEX(A2:D6,MATCH(1,(A2:A6="PETER")*(B2:B6=H1)*((C2:C6="AM")+(C2:C6="Full day")),0),4)

The "and" condition you found works with multiply.
An "or" condition works with addition.

Book1
	A	B	C	D	E	F	G	H
1	Staff	Date	Time	Result		Peter Time	14-Apr	15-Apr
2	Mike	1-Apr	AM	Mike / Scheduled		AM or Full Day	Peter / Scheduled	#N/A
3	Sally	14-Apr	PM	Sally / Scheduled
4	Peter	14-Apr	PM	Peter / Scheduled		PM	Peter / Scheduled	Peter / Scheduled
5	Peter	14-Apr	Full Day	Peter / Scheduled
6	Peter	15-Apr	PM	Peter / Scheduled
Sheet1

Cell Formulas
Range	Formula
G2:H2	G2	=INDEX($A$2:$D$6,MATCH(1,($A$2:$A$6="PETER")*($B$2:$B$6=G1)*(($C$2:$C$6="AM")+($C$2:$C$6="Full Day")),0),4)
G4:H4	G4	=INDEX($A$2:$D$6,MATCH(1,($A$2:$A$6="PETER")*($B$2:$B$6=G1)*($C$2:$C$6="PM"),0),4)

THANK YOU BOTH SO MUCH! I have spent the last two days pulling my hair out trying to resolve this! Thanks, this will make my work so much easier!

 

[Fluff]

Glad we could help & thanks for the feedback.