How to compare 2 columns in one table with 2 columns in another table?

[JenniferMurphy]

I want to create a sheet that I can use to rate soups that I buy and try. I have two tables as shown below. I have included the table names in the top row. They are not displayed by xl2bb.

TblProducts is a list of the products that I have tried. There is one record for each different soup product. There cannot be any duplicates.

TblLog is a list of the tests of these soups. Each soup in TblProducts can be listed zero or more times.

The Ok? column in TblLog tests whether the soup in that row exists in TblProducts. The only way I could think of to make this work is to create the helper column Brand+Flavor in TblProducts. Is there a better way that does not require the helper column?

Mr Excel.xlsx
	B	C	D	E	F	G	H	I
3	Table name: TblProducts				Table name: TblLog
4	Brand	Flavor	Brand+Flavor		Date	OK?	Brand	Flavor
5	Campbell's	Chunky Beef with Vegetables	Campbell's/Chunky Beef with Vegetables		3/04/23	Ok	Campbell's	Chunky Beef with Vegetables
6	Campbell's	Chunky Chicken and Sausage Gumbo	Campbell's/Chunky Chicken and Sausage Gumbo		3/07/23	Ok	Progresso	Chicken Noodle
7	Campbell's	Chunky Chicken Noodle	Campbell's/Chunky Chicken Noodle		6/16/23	Ok	Campbell's	Chunky Beef with Vegetables
8	Progresso	Chicken & Homestyle Noodles	Progresso/Chicken & Homestyle Noodles		6/23/23	Ok	Progresso	Chicken Noodle
9	Progresso	Chicken Noodle	Progresso/Chicken Noodle		7/04/23	Ok	Progresso	Chicken Noodle
10					8/01/23	Error	Campbell's	Tomato
11					9/01/23	Error	Progresso	Lentil
2-column compare

Cell Formulas
Range	Formula
D5:D9	D5	=[@Brand] & "/" & [@Flavor]
G5:G11	G5	=IF(COUNTIFS(TblProducts[Brand+Flavor],[@Brand]&"/"&[@Flavor])=1,"Ok","Error")

 

[etaf]

just a countifs () should work without the helper
countifs( brandrange in tblproducts , brand cell in tbllog , flavour range in tbleprodcuts, flavour cell in tbllog)
copied down
use in an IF , as you have done, to get OK

 

[JenniferMurphy]

just a countifs () should work without the helper
countifs( brandrange in tblproducts , brand cell in tbllog , flavour range in tbleprodcuts, flavour cell in tbllog)
copied down
use in an IF , as you have done, to get OK

I should have tried that. I thought it would test each compare separately. Thank you.

Mr Excel.xlsx
	B	C	D	E	F	G	H
3	Table name: TblProducts			Table name: TblLog
4	Brand	Flavor		Date	Ok?	Brand	Flavor
5	Campbell's	Chunky Beef with Vegetables		3/04/23	Ok	Campbell's	Chunky Beef with Vegetables
6	Campbell's	Chunky Chicken and Sausage Gumbo		3/07/23	Ok	Progresso	Chicken Noodle
7	Campbell's	Chunky Chicken Noodle		6/16/23	Ok	Campbell's	Chunky Beef with Vegetables
8	Progresso	Chicken & Homestyle Noodles		6/23/23	Ok	Progresso	Chicken Noodle
9	Progresso	Chicken Noodle		7/04/23	Ok	Progresso	Chicken Noodle
10				8/01/23	Error	Campbell's	Tomato
11				9/01/23	Error	Progresso	Lentil
2-column compare

Cell Formulas
Range	Formula
F5:F11	F5	=IF(COUNTIFS(TblProducts[Brand],[@Brand],TblProducts[Flavor],[@Flavor])=1,"Ok","Error")

 

[etaf]

you are welcome
countifs() looks at all the columns to match - its and AND , if you wanted an OR - you can + countifs() together or use an array {}

 

[JenniferMurphy]

you are welcome
countifs() looks at all the columns to match - its and AND , if you wanted an OR - you can + countifs() together or use an array {}

Thanks

 

[JenniferMurphy]

Thanks for the help. Here's a more elaborate solution.

Mr Excel.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Table name: TblProducts									Table name: TblLog
4	Brand	Flavor	Count	Average Rating	High Rating	Low Rating	First	Last		Date	Ok?	Brand	Flavor	Rating
5	Campbell's	Chunky Chicken and Sausage Gumbo	0	n/a	n/a	n/a	n/a	n/a		3/04/23	Ok	Campbell's	Chunky Beef with Vegetables	75
6	Progresso	Chicken & Homestyle Noodles	1	95.0	95.0	95.0	10/30/23	10/30/23		3/07/23	Ok	Progresso	Chicken Noodle	85
7	Campbell's	Chunky Chicken Noodle	2	87.5	93.0	82.0	9/25/23	12/29/23		6/16/23	Ok	Campbell's	Chunky Beef with Vegetables	77
8	Progresso	Chicken Noodle	4	84.5	90.0	75.0	3/07/23	11/13/23		6/23/23	Ok	Progresso	Chicken Noodle	88
9	Campbell's	Chunky Beef with Vegetables	2	76.0	77.0	75.0	3/04/23	6/16/23		7/04/23	Ok	Progresso	Chicken Noodle	90
10										8/01/23	Error	Campbell's	Tomato
11										9/01/23	Error	Progresso	Lentil
12										9/25/23	Ok	Campbell's	Chunky Chicken Noodle	93
13										10/30/23	Ok	Progresso	Chicken & Homestyle Noodles	95
14										11/13/23	Ok	Progresso	Chicken Noodle	75
15										12/29/23	Ok	Campbell's	Chunky Chicken Noodle	82
2-column compare

Cell Formulas
Range	Formula
D5:D9	D5	=COUNTIFS(TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor])
E5:E9	E5	= IF([@Count]>0,AVERAGEIFS(TblLog[Rating],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
F5:F9	F5	= IF([@Count]>0,MAXIFS(TblLog[Rating],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
G5:G9	G5	= IF([@Count]>0,MINIFS(TblLog[Rating],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
H5:H9	H5	= IF([@Count]>0,MINIFS(TblLog[Date],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
I5:I9	I5	= IF([@Count]>0,MAXIFS(TblLog[Date],TblLog[Brand],[@Brand],TblLog[Flavor],[@Flavor]),"n/a")
L5:L15	L5	=IF(COUNTIFS(TblProducts[Brand],[@Brand],TblProducts[Flavor],[@Flavor])=1,"Ok","Error")