Comparing a unique ID + corresponding dollar value

[GuineaPug]

Hi all,

Relatively new to formulas in Excel and trying to figure out how to match 2 values and then check their corresponding price in the next column.

In the example table below:

- Col A contains Unique ID 1, while Col B contains the corresponding price for Unique ID 1
- Col C contains Unique ID 2, while Col D contains the corresponding price for Unique ID 2
- Col E is to check if there are duplicates of Unique IDs between Col A and Col C, which could happen in this scenario I'm trying to recreate. I would need to know if a duplicate exists.
- Col F is to check if, in the case of any match(s) of Unique IDs between Cols A and C, there are then corresponding match(s) of price for the given Unique ID.

Hope the above is clear but please let me know if not!

I have filled out the expected results for Column E and F manually, however I'm not sure what formulas I would need to actually return those values automatically. Any help for this newbie would be much appreciated

Unique No.1	Price	Unique No.2	Price	Duplicates?	Price Match(s) found?
82362​	$100​	885658​	$150​	Yes	Yes
2563252​	$565​	2563252​	$565​	No	Yes
5346​	$500​	6437​	$890​	No	Yes
4234​	$766​	5346​	$500​	Yes	Yes
6437​	$890​	4234​	$745​	No	No
36432​	$150​	5346​	$500​	Yes	Yes
885658​	$677​	82362​	$100​	No	Yes
6363​	$533​	36432​	$200​	No	No
		885658​	$677​	Yes	Yes
		6363​	$533​	No	Yes

 

[kevin9999]

You have 2 x 885658 and 2 x 5346 in Unique No.2. What do you mean by "Unique"?

 

[GuineaPug]

You have 2 x 885658 and 2 x 5346 in Unique No.2. What do you mean by "Unique"?

Sorry! I should have clarified. Col A contains truly unique IDs - there won't be duplicates in this column. Col C however can contain duplicates of those IDs.

 

[kevin9999]

Sorry! I should have clarified. Col A contains truly unique IDs - there won't be duplicates in this column. Col C however can contain duplicates of those IDs.

In column E - Duplicates - you have several "No"s, but every ID in column C can be found in column A. I'm not following your logic, could you elaborate?

 

[kevin9999]

Based entirely on your image in post #1, see if the following is what you're after. Just adjust the ranges to suit.

Book1
	A	B	C	D	E	F
1	Unique No.1	Price	Unique No.2	Price	Duplicates?	Price Match(s) found?
2	82362	$100	885658	$150	Yes	No
3	2563252	$565	2563252	$565	No	Yes
4	5346	$500	6437	$890	No	Yes
5	4234	$766	5346	$500	Yes	Yes
6	6437	$890	4234	$745	No	No
7	36432	$150	5346	$500	Yes	Yes
8	885658	$677	82362	$100	No	Yes
9	6363	$533	36432	$200	No	No
10			885658	$677	Yes	Yes
11			6363	$533	No	Yes
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=IF(COUNTIF($C$2:$C$11,C2)>1,"Yes","No")
F2:F11	F2	=IF(D2=VLOOKUP(C2,$A$2:$B$11,2,FALSE),"Yes","No")

 

[Fluff]

My take on this.

Fluff.xlsm
	A	B	C	D	E	F
1	Unique No.1	Price	Unique No.2	Price	Duplicates?	Price Match(s) found?
2	82362	£100	885658	£150	Yes	No
3	2563252	£565	2563252	£565	No	Yes
4	5346	£500	6437	£890	No	Yes
5	4234	£766	5346	£500	Yes	Yes
6	6437	£890	4234	£745	No	No
7	36432	£150	5346	£500	Yes	Yes
8	885658	£677	82362	£100	No	Yes
9	6363	£533	36432	£200	No	No
10			885658	£677	Yes	Yes
11			6363	£533	No	Yes
Lists

Cell Formulas
Range	Formula
E2:E11	E2	=IF(COUNTIFS(C:C,C2)>1,"Yes","No")
F2:F11	F2	=IF(SUMIFS(B:B,A:A,C2)=D2,"Yes","No")

 

[GuineaPug]

@Fluff @kevin9999 - thank you so much! Both of your responses are solutions that work and give the solution I needed. Thanks again.

 

[Fluff]

Glad we could help & thanks for the feedback.