Check if a column's value is on another sheet and return specific value

[steve400243]

Hello Forum, On this workbook I need a way to return the value "YES" in column L on 276 Tab if the number in column B on the 276 Tab, Matches the number from column H On Sheet 1 Tab, and the value from Column I is "W/O", "Split", or "Split?". If not return "NO". Hope that makes Sense. Thanks for all help provided.

Book1
	A	B	C	D	E	F	G	H	I
1								Entry_Number	Findings
2								79905864419
3								79910844927	Bill Completed
4								79998154586	W/O
5								79998156730	W/O
6								79900239005	Bill Completed
7								79910842764	Billable
8								79910998004	Billable
9								79911380301	Split
10								79911380301	Split
11								79911439552	Split
12								79911599587	Billable
13								79911763134	W/O
14								79911776300	Billable
15								79911920221	Billable
16								79911920221	Split?
17								79911932002	Split?
18								79911955011	Billable
19								79911965127	W/O
20								79912057056	W/O
21								79912058385	W/O
22								79912202868	W/O
23								79912299690	W/O
24								79912584950	W/O
25
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	bill type	entry	port	rel date	master	bill create	bill compete	sct	ext	amt	w/o completed	w/o - YES or NO
3		79907546477
4		79912202868
5		79908167547
6		79909331068
7		79907208250
11		79909135949
13		79909652638
14		79908684269
15		79909867897
16		79913282547
19		79908382807
20		79905832978
21		79912057056
22		79908793623
26		79909345514
27		79908037401
29		79913367652
30		79910388081
31		79908990724
32		79913040366
33		79910630334
34		79908733470
35		79908440134
36		79909958076
37		79906954938
38		79914026174
39		79913936340
41		79908762404
55		79907099048
58		79907767065
60		79909478794
276

 

[gifariz]

How about

=IF(OR({"W/O";"Split";"Split?"}=LOOKUP(B3,H:H,I:I)),"YES","NO")

 

[Fluff]

Another option

=IF(SUM(COUNTIFS(Sheet1!H:H,B2,Sheet1!I:I,{"W/P","Split","Split~?"})),"Yes","No")

 

[steve400243]

How about

=IF(OR({"W/O";"Split";"Split?"}=LOOKUP(B3,H:H,I:I)),"YES","NO")

Thanks for looking at it gifariz, but Fluff answer provided my needed results.

 

[steve400243]

Another option

=IF(SUM(COUNTIFS(Sheet1!H:H,B2,Sheet1!I:I,{"W/P","Split","Split~?"})),"Yes","No")

Thank you again Fluff. This provides my needed results.

 

[Fluff]

Glad we could help & thanks for the feedback.