Matching by multiple criteria with the last being partial

FriendshipWaffles

New Member
Joined
Oct 12, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi All -- I cannot for the life of me figure this out. I have tried variations of IF, COUNTIFS and INDEX/MATCH with some ISNUMBER thrown in with wildcards in the works too. If anyone would be able to help me I would so appreciate it.

I am trying to make sure all of the procedures done on a specific patient (Report A) have been charged on their bill (Report B). I have three criteria that need to be reconciled -- the patient's eight digit unique identifying number, the date of service, and the actual procedural code. Some patients have multiple procedures across multiple days but would have the same eight digit number. The issue I am running into is that the third criteria (procedural code) listed on Report B has additions to it (like instead of code 12345 it lists 12345-AB-123).

How can I make sure I match all three criteria -- that it's the right patient (eight digit number), date of service second, and then procedure code BUT I only need the partial match (12345). I need the result to just tell me "Yes Match" or "No Match" or some variation of that. This is done across thousands of records on a regular basis so I would love to not have to do text to columns or similar.

Example reports below (I can't download the add-in at work, sorry!!). So the first three would be a match, the fourth would not reconcile.

1665593955985.png
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Like this?

=IF(COUNTIFS(ReportA!A:A,A2,ReportA!B:B,B2,ReportA!C:C,LEFT(C2,5)),"Match","No Match")
 
Upvote 0
Solution
Sometimes there is no data in the procedural code column and I want to ignore the formula (not have it return a "No Match"). How can I add that to the formula above?

Thanks again!
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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