Multiple search conditions to find value

[MARGI123]

Hello,

I need to find payment information based on 3 conditions. This include the claim ID, charge from date, and then the cpt code. Based on these conditions I need it to return the allowed amount and then the paid amount. I have 2 workbooks that contain the information. One is the charge overview which will be called table 1 below and the payment information or table 2. See below.

TABLE 1

CLAIM ID CHARGE FROM DATE (MIN) CHARGE TO DATE (MAX) CHARGE CPT CODE CHARGE AMOUNT ALLOWED AMOUNT PAID AMOUNT
127237110 07/08/2019 07/11/2019 90834 $3,600.00
127237110 07/09/2019 07/12/2019 90853 $2,400.00
127237200 07/15/2019 07/19/2019 90853 $3,600.00
127237200 07/17/2019 07/18/2019 90834 $2,400.00

TABLE 2

Charge Claim ID Charge CPT Code Charge From Date (Min) Charge To Date (Max) Payment Allowed Amount (Sum) Insurance Paid Amount (Sum)
127237110 90853 07/09/2019 07/12/2019 $1,680.00 $1,680.00
127237110 90834 07/08/2019 07/11/2019 $2,520.00 $2,520.00
127237200 90834 07/17/2019 07/18/2019 $2,400.00 $700.00
127237200 90853 07/15/2019 07/19/2019 $3,600.00 $1,050.00



I've asked for help on this before, but unfortunately I do not remember what the formula was. It worked perfectly.

[CARACALLA]

CHARGE OVERWIEW

L2=IF('CHARGE OVERVIEW'!B2<>"",SUMIFS(PAYMENT!G$2:G$5,PAYMENT!$A$2:$A$5,'CHARGE OVERVIEW'!$B2,PAYMENT!$D$2:$D$5,'CHARGE OVERVIEW'!$D2,PAYMENT!$F$2:$F$5,'CHARGE OVERVIEW'!$J2,PAYMENT!$C$2:$C$5,'CHARGE OVERVIEW'!$G2,PAYMENT!$E$2:$E$5,'CHARGE OVERVIEW'!$E2),"")


Copy across and down

[MARGI123]

Sorry, I don't under stand parts of your formula. Where does the column G come in if there is no data in that column per my report? Also, it did not work. States there are circular references.

[MARGI123]

I feel like this is very close to what was given before, but it's not working.

[CARACALLA]

I attach the file

[MARGI123]

I'm missing something. How are you entering this? is this an array?

[MARGI123]

Okay I finally figured it out! Thanks for all the help

[MARGI123]

I made one minor modification due to it not pulling info. I removed the search for units and days billed. For some reason my report isn't matching on that section. Other than that it works perfectly.

[MARGI123]

Hello,

I was given this formula to search multiple identifiers related to a claim to identify payments. It works for the most part, but I noticed that it wasn't working due to another identifier needing to be added. I tried adding this to the formula, but it's not working. I have highlighted the columns with the formula and the errors are in red text.

[JeteMc]

It may help us to know what identifier needs to be added to the formula. It may also help if you could tell us the values that should be in cells C11, P11:Q11.
Let us know if you have any questions.

[MARGI123]

Identifiers currently in the formula are Claim ID, Charge From Date, Charge To Date, and CPT Code. The original Formula also included Charge Units, but I found that it was not as accurate and removed it. I wanted to add Rev Code to the formula, but it doesn't seem to be helping. What I want the formula to do is identify all these items and find the payment related to that line in the AR Report tab. Our system sometimes breaks claims if they have different CPT codes, Rev codes, or payments are split. So C11 I need the Insurance Paid Date, P11 I need the Allowed Amount from the Payment, and Q11 I need the Insurance Paid Amount. Line 11 is highlighted because the data is in the AR report, but the formula is not pulling it.

[JeteMc]

It is helpful to volunteers that are taking their time to resolve issues if the people asking will specify what values they expect as in C11 should be 4/22/2019 and/or C11 should display the value from AR REPORT - PAYMENT INFO!A5
That said, it seems that one possible solution would be to use SUMPRODUCT and not include the first row of values from the AR REPORT - PAYMENT INFO sheet, so:
For C3 and down:

Please Login or Register  to view this content.

For P3 and down:

Please Login or Register  to view this content.

For Q3 and down:

Please Login or Register  to view this content.

Note that these are not array entered formulas (just press the Enter key to activate before copying down).
Note that the range could be increased to extend down as far as needed by changing all of the 20's in the formulas.
Let us know if you have any questions.

[MARGI123]

Thank you for your help! That worked perfectly. I'll experiment with a larger amount of data and if I have further questions, I will let you know.

[JeteMc]

You're Welcome and thank you for the feedback. If you experimentation proves satisfactory, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.