Help with formula matching 2 columns verse 2 other columns

[zone709]

Hi trying to have a certain kind of formula in column E so it can tell me if its a match or not. A & B would have to match C & D or not. Just the ID and amount verse each other.
I can do the formula to match one column verse the other but having a hard time on two columns matches thanks.

Book1
	A	B	C	D	E
1	ID	Amount	ID	Amount	Forumla Needed
2	4	$ 25.00	4	25	Match
3	5	$ 50.00	5	55	No Match
4	7	$ 75.00	7	70	No Match
Sheet1

 

[Cubist]

How about

Book2
	A	B	C	D	E
1	ID	Amount	ID	Amount	Forumla Needed
2	4	25	4	25	match
3	5	50	5	55	no match
4	7	75	7	70	no match
Sheet5

Cell Formulas
Range	Formula
E2:E4	E2	=IF(A2:A4&B2:B4=C2:C4&D2:D4,"match","no match")
Dynamic array formulas.

 

[zone709]

How about

Book2
	A	B	C	D	E
1	ID	Amount	ID	Amount	Forumla Needed
2	4	25	4	25	match
3	5	50	5	55	no match
4	7	75	7	70	no match
Sheet5

Cell Formulas
Range	Formula
E2:E4	E2	=IF(A2:A4&B2:B4=C2:C4&D2:D4,"match","no match")
Dynamic array formulas.

Thank you for you help.

 

[Cubist]

Taking a second look the above solution. There's a scenario that would fail. This one should be better.

Book1
	A	B	C	D	E	F
1	ID	Amount	ID	Amount	Forumla Needed
2	4	25	4	25	match
3	5	50	5	55	no match
4	7	75	7	70	no match
5	42	5	4	25	no match
6	42	5	4	25	match	<-Old formula is wrong
Sheet4

Cell Formulas
Range	Formula
E2:E5	E2	=IF(AND(A2=C2,B2=D2),"match","no match")
E6	E6	=IF(A6&B6=C6&D6,"match", "no match")

 

[zone709]

Hi thanks for the reply. You know what. I just ran into another issue small.

I need to change this a little. I need it to look though the whole column for id and amount to give me results on a match or not. Most of the time the columns of ID's wont be in the same line number.
Example below.
I yellowed the ones that matched and oranged the ones that didn't match.

Book1
	A	B	C	D	E
1	ID	AMOUNT	ID	AMOUNT	Formula
2	4	$ 25.00	8	$ 21.00	Match
3	5	$ 23.00	4	$ 20.00	No Match
4	7	$ 12.00	10	$ 21.00	No Match
5	8	$ 21.00	7	$ 12.00	Match
6	10	$ 22.00	5	$ 23.00	Match
Sheet1

 

[Cubist]

How about this? In the case the ID in col A is not found in col C, it'll return "not found".

Book1
	A	B	C	D	E
1	ID	AMOUNT	ID	AMOUNT	Formula
2	4	25	8	21	no match
3	5	23	4	20	match
4	7	12	10	21	match
5	8	21	7	12	match
6	10	22	5	23	no match
Sheet7

Cell Formulas
Range	Formula
E2:E6	E2	=LET(b,BYROW(A2:A6,LAMBDA(r,XLOOKUP(r,C2:C6,D2:D6,,0)))=B2:B6,IF(ISERROR(b),"not found",IF(b,"match","no match")))
Dynamic array formulas.

 

[zone709]

The array works great. This is a much better option. Thanks for the help again.

 

[zone709]

Hi wait i been trying this formula right and it wasn't working. Even in your sheet above.
Look at C2 and D2 it says no match next to it, but it matches A5 and B5 Both match ID 8 & 21 - So it should come back Match

 

[Cubist]

The match and found result is currently based on A not C. Do you want it to be based on column C?

A2 is ID 4 with an amount 25, which does not match amount of 20 in D. It returns no match for A2.

 

[zone709]

ok i get it now. yeah rather go off of C.
When i drag this formula down i think ill need to at least lock the A, C, D & B ike below?
Or else the formula wont look at above ID's #'s above the drag down. When i drag it down to lets say line 10. Its not looking above it anymore.

=LET(b,BYROW(A$2:A6,LAMBDA(r,XLOOKUP(r,C$2:C6,D$2:D6,,0)))=B$2:B6,IF(ISERROR(b),"not found",IF(b,"match","no match")))