Spreadsheet: 785656d1656527050-needing-xlookup-to-cross-reference-multiple-criteria-book
Sheet: Sheet1

EOB/WAYSTAR AUDIT
EOB LIST | EOB/BATCH NUM. | CHECK NUM. | DUP./UNIQUE | EOB DATE | [NAME] | INS. | PMT. SOURCE | EOB CHECK TOTAL | EOB AMT. | [NAME]. | Data Source CHECK AMT. | Data Source PMT. AMT. | Data Source DIFF. | Data Source PMT. AUDIT | DISCREPANCY SOURCE | WAYSTAR AMT. | THREE WAY MATCH | NOTES
Miami | XQ1784 | XQ-1784 |  | 2022-05-02 00:00:00 | Miami |  |  | 2100 | 2100 |  |  |  | 0 | Blank | Blank
Miami | JY5913 | JY-5913 |  | 2022-05-03 00:00:00 | Miami |  |  | 610 | 610 |  |  |  | 0 | Blank | Blank
Miami | RR3760 | RR-3760 |  | 2022-05-02 00:00:00 | Miami |  |  | 1050 | 1050 |  |  |  | 0 | Blank | Blank
Miami | CE1527 | CE-1527 |  | 2022-05-04 00:00:00 | Miami |  |  | 210 | 210 |  |  |  | 0 | Blank | Blank
Miami | OE1818 | OE-1818 |  | 2022-05-03 00:00:00 | Miami |  |  | 2225 | 2225 |  |  |  | 0 | Blank | Blank
Miami | UY1713 | UY-1713 |  | 2022-05-09 00:00:00 | Miami |  |  | 105 | 105 |  |  |  | 0 | Blank | Blank
Miami | XN1746 | XN-1746 |  | 2022-05-10 00:00:00 | Miami |  |  | 1890 | 1890 |  |  |  | 0 | Blank | Blank
Miami | EE6115 | EE-6115 |  | 2022-05-09 00:00:00 | Miami |  |  | 125.93 | 125.93 |  |  |  | 0 | Blank | Blank
Miami | SQ4870 | SQ-4870 |  | 2022-05-11 00:00:00 | Miami |  |  | 840 | 840 |  |  |  | 0 | Blank | Blank
Miami | LR7886 | LR-7886 |  | 2022-05-10 00:00:00 | Miami |  |  | 4335.4 | 4335.4 |  |  |  | 0 | Blank | Blank
Miami | RR2763 | RR-2763 |  | 2022-05-12 00:00:00 | Miami |  |  | 420 | 420 |  |  |  | -50 | Blank | Blank
Miami | WT7869 | WT-7869 |  | 2022-05-13 00:00:00 | Miami |  |  | 105 | 105 |  |  |  | 0 | Blank | Blank
Miami | US7482 | US-7482 |  | 2022-05-17 00:00:00 | Miami |  |  | 439 | 439 |  |  |  | 0 | Blank | Blank
Miami | WB1636 | WB-1636 |  | 2022-05-16 00:00:00 | Miami |  |  | 2310 | 2310 |  |  |  | 0 | Blank | Blank
Miami | RA2709 | RA-2709 |  | 2022-05-16 00:00:00 | Miami |  |  | 840 | 840 |  |  |  | 0 | Blank | Blank
Miami | KG5716 | KG-5716 |  | 2022-05-17 00:00:00 | Miami |  |  | 2110 | 2110 |  |  |  | 0 | Blank | Blank
Miami | WR1348 | WR-1348 |  | 2022-05-19 00:00:00 | Miami |  |  | 334 | 334 |  |  |  | 0 | Blank | Blank
Miami | GV2842 | GV-2842 |  | 2022-05-20 00:00:00 | Miami |  |  | 305 | 305 |  |  |  | 0 | Blank | Blank
Miami | DP8632 | DP-8632 |  | 2022-05-19 00:00:00 | Fresno |  |  | 8850 | 8850 |  |  |  | 0 | Blank | Blank
Miami | IG9979 | IG-9979 |  | 2022-05-19 00:00:00 | Tampa |  |  | 17925 | 10875 |  |  |  | 2150 | 50 | Tampa
Miami | IG9979 | IG-9979 |  | 2022-05-19 00:00:00 | San Diego |  |  | 17925 | 300 |  |  |  | 2150 | 100 | San Diego
Miami | IG9979 | IG-9979 |  | 2022-05-19 00:00:00 | Houston |  |  | 17925 | 1500 |  |  |  | 2150 | 2000 | Houston
Miami | IG9979 | IG-9979 |  | 2022-05-19 00:00:00 | Miami |  |  | 17925 | 5250 |  |  |  | 2150 | Blank | Blank
Miami | XQ8098 | [NAME] |  | 2022-05-23 00:00:00 | Miami |  |  | 2415 | 2415 |  |  |  | 0 | Blank | Blank
Miami | AT8722 | AT-8722 |  | 2022-05-23 00:00:00 | Miami |  |  | 1384.1 | 1384.1 |  |  |  | 0 | Blank | Blank
Miami | AN6972 | AN-6972 |  | 2022-05-31 00:00:00 | Miami |  |  | 210 | 210 |  |  |  | 0 | Blank | Blank
Miami | JP2070 | [NAME] |  | 2022-05-31 00:00:00 | Miami |  |  | 2310 | 2310 |  |  |  | 0 | Blank | Blank
Miami | RS3003 | RS-3003 |  | 2022-05-31 00:00:00 | Miami |  |  | 2310 | 2310 |  |  |  | 0 | Blank | Blank
Miami | UD7709 | UD-7709 |  | 2022-05-31 00:00:00 | Miami |  |  | 945 | 945 |  |  |  | 0 | Blank | Blank
Miami | QG4849 | QG-4849 |  | 2022-05-31 00:00:00 | San Diego |  |  | 668 | 668 |  |  |  | 0 | Blank | Blank
Miami | ZX1995 | ZX-1995 |  | 2022-05-19 00:00:00 | San Diego |  |  | 134.1 | 134.1 |  |  |  | 0 | Blank | Blank
Miami | OP7836 | OP-7836 |  | 2022-05-19 00:00:00 | Houston |  |  | 583.2 | 583.2 |  |  |  | 0 | Blank | Blank
Fresno | PY3083 | [NAME] |  | 2022-05-02 00:00:00 | Fresno |  |  | 2929.5 | 2929.5 |  |  |  | 0 | Blank | Blank
Fresno | EK1670 | EK-1670 |  | 2022-05-02 00:00:00 | Fresno |  |  | 2976.12 | 2976.12 |  |  |  | 0 | Blank | Blank
Fresno | VB1499 | VB-1499 |  | 2022-05-02 00:00:00 | Fresno |  |  | 12295 | 12295 |  |  |  | 0 | Blank | Blank
Fresno | RH3233 | RH-3233 |  | 2022-05-02 00:00:00 | Fresno |  |  | 935 | 935 |  |  |  | 0 | Blank | Blank
Total
 |  |  |  |  |  |  |  |  |  | =AVERAGEIF([EOB/BATCH NUM.],B3,[EOB CHECK TOTAL])-SUMIF([EOB/BATCH NUM.],B3,[EOB AMT.]) | =XLOOKUP(B3,Raw!A:A,Raw!H:H) |  | =AVERAGEIF(Raw!A:A,B3,Raw!H:H)-SUMIFS(Raw!K:K,Raw!A:A,B3)
 |  |  |  |  |  |  |  |  |  |  |  | Need Help |  | Need Help | Need Help
 |  |  |  |  |  |  |  |  |  |  |  | I need to pull the information from the "Raw" tell me what the payment amounts are in column K |  | I need the output to tell me what the difference in the amount of payments are based on the EOB Batch # in this tab's Column B and the location. | Need the return to tell me what the location is that the payment amount in column K (Raw) is off.