Return "yes" or "no" matching multiple criteria

[MG137]

Good Afternoon,

I am trying to find a formula, function, anything really that can take the ID and Amount columns on the submissions list and return a "yes" or "no" if a row matching both criteria are found on the results list. The original lists have over 7 thousand rows so a side by side comparison would be time consuming and it is a task that will need to be completed on a weekly basis so I am looking for something that will help me find the items missing from the results list more efficiently. Any help would be appreciated.

 

[Coyotex3]

Are you looking for something like this

PERSONAL.xlsm
	A	B	C	D
1	ID			AMT
2	TD11			1,000.00
3	HK55			500.00
4	JK12			450.00
Submission List

PERSONAL.xlsm
	A	B	C	D	E	F
1	ID				Current Period Result
2	TD11				1,000.00	Yes
3	HK55				650.00	No
4	JK12				450.00	Yes
Result List

Cell Formulas
Range	Formula
F2:F4	F2	=IF(XLOOKUP(A2,'Submission List'!$A$2:$A$4,'Submission List'!$D$2:$D$4)='Result List'!E2,"Yes","No")

 

[MG137]

Thank you for that response but it turns out I have a different version of excel than I thought so this will not work for me. Is there an alternative to XLookUp for Excel 2016?

 

[Coyotex3]

Maybe this:

=IF(VLOOKUP(A2,'Submission List'!A1:D4,4,FALSE)=E2,"Yes","No")

PERSONAL.xlsm
	A	B	C	D	E	F
1	ID	Name	Type	AMT
2	TD11	A	B	1,000.00
3	HK55	A	B	500.00
4	JK12	A	B	450.00
5
6
Submission List

PERSONAL.xlsm
	A	B	C	D	E	F	G
1	ID	Name	Type	Data	Current Period Result	Match Found
2	TD11	A	B	C	1,000.00	Yes
3	TD11	A	B	C	999.00	No
4	JK12	A	B	C	450.00	Yes
5
Result List

Cell Formulas
Range	Formula
F2:F4	F2	=IF(VLOOKUP(A2,'Submission List'!A1:D4,4,FALSE)=E2,"Yes","No")

You will need to adjust the lookup range since you mentioned having about 7k rows.

 

[Alex Blakenburg]

Amount is generally not an ideal way of looking something up. In your data, you have IDs appearing multiple times.
If those multiple IDs then also have the same amount then the lookup will not necessarily give you the right result.
An additional complication is that you have numbers as text, this adds risk of the number being text on one sheet but a number on the other sheet.
Countifs (Sumifs etc) tend to be more forgiving on text-number mismatches.
I have included an option of doing 2 Countifs so that there are multiple ID/Amt lines it also matches how many on each sheet.

Here are some options, if they slow your spreadsheet down too much you might need to consider using VBA or Power Query.

20231116 Lookup 2 fields 2016 MG137.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ID			AMT	File	Source	Countifs	Lookup	Countifs x2 Matching Count	Comment
2	TD11			1000			Yes	Yes	Yes
3	HK55			500			No	No	No
4	JK12			450			Yes	Yes	Yes
5	XXXX			100			Yes	Yes	Yes
6	AAA			200			Yes	Yes	Check	Results has AAA/200 twice
7	0059			50			Yes	No	Yes	Countifs is more forgiving with data types ie Text here but Number in Result List
8	BBB			99			Yes	Yes	Check	Results has BBB/99 once but it is here twice
9	BBB			99			Yes	Yes	Check	Results has BBB/99 once but it is here twice
Submission List

Cell Formulas
Range	Formula
G2:G9	G2	=IF(COUNTIFS('Result List'!$A$2:$A$9,$A2,'Result List'!$E$2:$E$9,$D2),"Yes","No")
H2:H9	H2	=IF(ISNA(LOOKUP(2,1/(('Result List'!$A$2:$A$10=$A2)*('Result List'!$E$2:$E$10=$D2)),'Result List'!$A$2:$A$10)),"No","Yes")
I2:I9	I2	=IF(COUNTIFS('Result List'!$A$2:$A$9,$A2,'Result List'!$E$2:$E$9,$D2),IF(COUNTIFS('Result List'!$A$2:$A$9,$A2,'Result List'!$E$2:$E$9,$D2)=COUNTIFS($A$2:$A$10,$A2,$D$2:$D$10,$D2),"Yes","Check"),"No")

20231116 Lookup 2 fields 2016 MG137.xlsx
	A	B	C	D	E
1	ID				Current Period Result
2	TD11				1000
3	HK55				650
4	JK12				450
5	XXXX				100
6	AAA				200
7	AAA				200
8	59				50
9	BBB				99
Result List

 

[MG137]

Thank you both. I agree about using the amount and ID fields but my lists are combined data from about 12 other sheets and the ID and amount fields are the only consistent fields across all reports, leaving them to be the only fields I can use for this task.

I will try both suggestions.