Match for two possibilities in the same column

[yankat22]

Hi there,

I'm working on a self-scheduling sheet for a group of 30 employees and I'm trying to figure out how to match two criteria and it populate a single name (index:match type of function except you're matching for possibility of two). Example:
Everyone signs up in column B for Mondays shift and I want to search that column for either 2100B or 2100C and it populate the name that matches that specification in a single cell.

I've attached an example of the spreadsheet I'm working on. Any and all guidance is appreciated.

 

[SanjayGMusafir]

Hi there,

I'm working on a self-scheduling sheet for a group of 30 employees and I'm trying to figure out how to match two criteria and it populate a single name (index:match type of function except you're matching for possibility of two). Example:
Everyone signs up in column B for Mondays shift and I want to search that column for either 2100B or 2100C and it populate the name that matches that specification in a single cell.

I've attached an example of the spreadsheet I'm working on. Any and all guidance is appreciated.

Upload sample data using XL2BB utility. Can't work with screenshots to populate formulae.

 

[yankat22]

practicing formulas.xlsx
	UE	UF	UG
45	Thompson	24	2100B
46	garcia
47	taylor	2100A	2100A
48
49	lee	2130	2130
50	harris	2100c
51
52	0600 Starts	19	18
53	0600 Goal	16	16
54	BALANCE +/-	3	2
55	0600 Avg
56	1600 Staff	15	14
57	1700 Staff	14	13
58	24 or 12p	Thompson	jones
59	Lates	3	4
60	Late 2100A	clark	rodriguez
61	Late 2100B	#N/A	harris
62	Late 2130	lee	lee
Schedule

Cell Formulas
Range	Formula
UF52:UG52	UF52	=COUNTIF(UF$3:UF$51,13)+COUNTIF(UF$3:UF$51,10)+COUNTIF(UF$3:UF$51,"2100A")+COUNTIF(UF$3:UF$51,24)+COUNTIF(UF$3:UF$51,11)+COUNTIF(UF$3:UF$51,"2100B")+COUNTIF(UF$3:UF$51,7)
UF54:UG54	UF54	=UF52-UF53
UF56:UG56	UG56	=COUNTIF(UG$3:UG$51,13)+COUNTIF(UG$4:UG$51,2200)+COUNTIF(UG$4:UG$51,2100)+COUNTIF(UG$4:UG$51,2130)+COUNTIF(UG$4:UG$51,24)++COUNTIF(UG$4:UG$51,11)
UF57:UG57	UG57	=COUNTIF(UG$3:UG$51,13)+COUNTIF(UG$3:UG$51,2200)+COUNTIF(UG$3:UG$51,2100)+COUNTIF(UG$3:UG$51,2130)+COUNTIF(UG$3:UG$51,24)
UF58:UG58	UG58	=INDEX($UE$4:$UE$51,MATCH(24,UG$4:UG$51,0),1)
UF59:UG59	UG59	=COUNTIF(UG3:UG51,"2100A")+COUNTIF(UG4:UG51,"2130")+COUNTIF(UG4:UG51,"2100B")
UF60:UG60	UG60	=INDEX($B$3:$AY$51,MATCH("2100A",UG$3:UG$51,0),1)
UF61	UF61	=INDEX($B$3:$AY$51,MATCH("2100B",UF$3:UF$51,0),1)
UF62:UG62	UF62	=INDEX($B$3:$AY$51,MATCH(2130,UF$3:UF$51,0),1)

 

[yankat22]

This is a better version because UG 61 isn't formulated in the first response. Thank you for any and all help

practicing formulas.xlsx
	UE	UF	UG
45	Thompson	24	2100B
46	garcia
47	taylor	2100A	2100A
48
49	lee	2130	2130
50	harris	2100c
51
52	0600 Starts	19	18
53	0600 Goal	16	16
54	BALANCE +/-	3	2
55	0600 Avg
56	1600 Staff	15	14
57	1700 Staff	14	13
58	24 or 12p	Thompson	jones
59	Lates	3	4
60	Late 2100A	clark	rodriguez
61	Late 2100B	#N/A	Thompson
62	Late 2130	lee	lee
Schedule

Cell Formulas
Range	Formula
UF52:UG52	UF52	=COUNTIF(UF$3:UF$51,13)+COUNTIF(UF$3:UF$51,10)+COUNTIF(UF$3:UF$51,"2100A")+COUNTIF(UF$3:UF$51,24)+COUNTIF(UF$3:UF$51,11)+COUNTIF(UF$3:UF$51,"2100B")+COUNTIF(UF$3:UF$51,7)
UF54:UG54	UF54	=UF52-UF53
UF56:UG56	UF56	=COUNTIF(UF$3:UF$51,13)+COUNTIF(UF$4:UF$51,2200)+COUNTIF(UF$4:UF$51,2100)+COUNTIF(UF$4:UF$51,2130)+COUNTIF(UF$4:UF$51,24)++COUNTIF(UF$4:UF$51,11)
UF57:UG57	UF57	=COUNTIF(UF$3:UF$51,13)+COUNTIF(UF$3:UF$51,2200)+COUNTIF(UF$3:UF$51,2100)+COUNTIF(UF$3:UF$51,2130)+COUNTIF(UF$3:UF$51,24)
UF58:UG58	UF58	=INDEX($UE$4:$UE$51,MATCH(24,UF$4:UF$51,0),1)
UF59:UG59	UF59	=COUNTIF(UF3:UF51,"2100A")+COUNTIF(UF4:UF51,"2130")+COUNTIF(UF4:UF51,"2100B")
UF60:UG60	UF60	=INDEX($B$3:$AY$51,MATCH("2100A",UF$3:UF$51,0),1)
UF61:UG61	UF61	=INDEX($B$3:$AY$51,MATCH("2100B",UF$3:UF$51,0),1)
UF62:UG62	UF62	=INDEX($B$3:$AY$51,MATCH(2130,UF$3:UF$51,0),1)

 

[SanjayGMusafir]

See if this works for you -

=IFERROR(INDEX($B$3:$AY$51,MATCH("2100B",UF$3:UF$51,0),1),INDEX($B$3:$AY$51,MATCH("2100C",UF$3:UF$51,0),1))

 

[yankat22]

That works beautifully! Thank you so much!

The same formula should work if I change 2100b and 2100c to 12p and 24 right? I've been trying to plug it in other columns with those changes but only 12p is resulting in a name, 24 is saying #NA. Thank you again for your help!

 

[yankat22]

I figured it out, if I remove the quotation marks from the 24 or add a letter to the 24 in quotation marks, then it counts it. Thank you so much for your help!