How to Match Multiple Criteria, Exclude Specific Value, "Yes", "No"

[Kirstie99]

I have 2 tabs - one that shows items, status and price, and then another tab where I need a formula to say "Yes" or "No". I am trying to see if there are any other authorized sellers selling the same item number, no matter the price. Ideally I would like to say are there any other authorized sellers with an EXACT price match. Data is 10s of thousands of rows with over 3K items so it cannot be manual. I have used concatenate and look ups this way - but I feel like there should be a better/cleaner way to do this as I want this to be easy for others to use and not have so many columns. Any help is appreciated!

Item Number	Seller	Price	Status
1234	Bob	4.99	Authorized
2345	Henry	5.99	Authorized
5649	Phil	6.33	Authorized
1234	Eric	5.45	Unauthorized
5648	Adam	2.12	Unauthorized
1234	Dan	3.63	Authorized

Item	Seller	Any other Authorized?
1234	Bob
2345	Bob	Yes
5649	Bob
1234	Bob
5648	Bob
1234	Bob

 

[Kirstie99]

Oh and when I want it to say "Yes" "No" It would be like any authorized sellers excluding "Bob" because I want to see if someone else is also selling that item

 

[Cubist]

Try:

Book2
	A	B	C	D	E	F	G	H
1	tem Number	Seller	Price	Status		Item	Seller	Any other Authorized?
2	1234	Bob	4.99	Authorized		1234	Bob	Yes
3	2345	Henry	5.99	Authorized		2345	Bob	Yes
4	5649	Phil	6.33	Authorized		5649	Bob	Yes
5	1234	Eric	5.45	Unauthorized		1234	Bob	Yes
6	5648	Adam	2.12	Unauthorized		5648	Bob	No
7	1234	Dan	3.63	Authorized		1234	Bob	Yes
Sheet6

Cell Formulas
Range	Formula
H2:H7	H2	=IF(COUNTIFS($A$2:$A$7,F2,$B$2:$B$7,"<>"&G2,$D$2:$D$7,"Authorized"),"Yes","No")

 

[Kirstie99]

Try:

Book2
	A	B	C	D	E	F	G	H
1	tem Number	Seller	Price	Status		Item	Seller	Any other Authorized?
2	1234	Bob	4.99	Authorized		1234	Bob	Yes
3	2345	Henry	5.99	Authorized		2345	Bob	Yes
4	5649	Phil	6.33	Authorized		5649	Bob	Yes
5	1234	Eric	5.45	Unauthorized		1234	Bob	Yes
6	5648	Adam	2.12	Unauthorized		5648	Bob	No
7	1234	Dan	3.63	Authorized		1234	Bob	Yes
Sheet6

Cell Formulas
Range	Formula
H2:H7	H2	=IF(COUNTIFS($A$2:$A$7,F2,$B$2:$B$7,"<>"&G2,$D$2:$D$7,"Authorized"),"Yes","No")

This worked perfectly!! Thank you so much!!!!!