Help with formula matching 2 columns verse 2 other columns

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1ID Amount IDAmountForumla Needed
24$ 25.00425Match
35$ 50.00555No Match
47$ 75.00770No Match
Sheet1
 
I've changed the formula to go off of C. It's a dynamic array so you shouldn't have to drag down. Make sure you grab all the ranges. If your data last row is 10 it should be A2:A10... same for the other ranges.
Book1
ABCDE
1ID AMOUNT ID AMOUNT Formula
2425821match
3523420no match
47121021no match
5821712match
61022523match
Sheet8
Cell Formulas
RangeFormula
E2:E6E2=LET(b,BYROW(C2:C6,LAMBDA(r,XLOOKUP(r,A2:A6,B2:B6,,0)))=D2:D6,IF(ISERROR(b),"not found",IF(b,"match","no match")))
Dynamic array formulas.
 
Upvote 0
Solution

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top