Function Problems

How to Find the Diagnosis with the Highest Paid Amount in Excel

Maira Mehtab Sep 24, 2026 869 views

Question details

The user needs to identify and extract the diagnosis and its corresponding highest employer-paid amount for each individual member ID within an Excel dataset.

Product: Excel
Device & OS: not provided
Scenario: Analyzing medical or insurance billing data to identify the most expensive diagnosis associated with specific members.
Observed behavior: The user needs a formulaic approach to group or filter by member ID and return the diagnosis associated with the maximum paid amount for that member.

Before you start

Ensure your data is organized in clean columns (e.g., Member ID, Paid Amount, and Diagnosis) without merged cells, and verify that the paid amounts are formatted as numbers.

Solution 1Recommended

Use XLOOKUP and MAXIFS Formulas (Newer Excel Versions)

For Excel 2019, Office 365, or newer, you can use modern functions like MAXIFS and XLOOKUP to easily retrieve the maximum value and matching diagnosis without needing complex array formulas.

Modern spreadsheet software supports dynamic arrays and multiple-criteria lookups natively, making this the cleanest and most efficient approach for processing large datasets.

Calculate the maximum paid amount

In an empty cell (e.g., L6), use the MAXIFS function to find the highest amount for the member ID located in cell J6: type `=MAXIFS(PaidAmount_Range, MemberID_Range, J6)`.

Extract the corresponding diagnosis

In the adjacent cell (e.g., K6), use XLOOKUP with multiple criteria to find the matching diagnosis: type `=XLOOKUP(1, (MemberID_Range=J6)*(PaidAmount_Range=L6), Diagnosis_Range)`.

Press Enter to apply

Press the Enter key. Because newer versions of Excel support dynamic arrays, you do not need special keystrokes to evaluate the multiple criteria logic.

Dynamic Arrays Alternative: You can also use the FILTER and SORT functions nested together to filter the dataset for the specific member and sort it descending by paid amount to extract the top row.

Solution 2

Use INDEX, MATCH, MAX, and IF Formulas (Older Excel Versions)

This legacy array-formula method works universally across older versions of Excel to conditionally find the maximum value for a specific member ID and return the associated diagnosis.

Analyze Billing and Medical Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, XLOOKUP, and MAXIFS, making it incredibly easy to extract the highest paid diagnosis for specific member IDs. It provides a robust, seamless data analysis experience.

1. Open your data file: Launch WPS Spreadsheet and open your billing, insurance, or medical data workbook.
2. Apply the MAXIFS function: Use the MAXIFS formula in your target cell to instantly calculate the highest paid amount per member ID.
3. Lookup the correct diagnosis: Enter the XLOOKUP function to cross-reference the member ID and maximum amount, seamlessly retrieving the corresponding diagnosis.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Native support for modern lookup functions like XLOOKUP and MAXIFS.Lightweight, fast, and completely free to use for your daily data processing tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH array formula return a #VALUE! error?

In older versions of Excel, formulas containing conditional arrays like IF must be entered as an array formula. Click into the formula bar and press Ctrl+Shift+Enter instead of just Enter.

What happens if there is a tie for the highest paid amount for the same member?

The standard INDEX/MATCH or XLOOKUP approach will return only the first diagnosis it encounters that matches the maximum amount. To return all tied diagnoses, you would need to use the FILTER function.

Can I use PivotTables instead of formulas to find the highest diagnosis?

Yes, you can create a PivotTable with 'Member ID' in the Rows area, 'Diagnosis' below it, and 'Max of Paid Amount' in the Values area. However, sorting it to show only the absolute top diagnosis per member requires applying a Top 10 (set to 1) Value Filter on the Diagnosis field.

Excel & Data Problems