Function Problems

How to Compare Rates for Matching Addresses in Excel

Algirdas Jasaitis Sep 27, 2026 869 views

Question details

Users need to verify if all services at a specific address use a consistent billing rate, even when the number of services fluctuates.

How to Compare Rates for Matching Addresses in Excel

Product: Excel
Device & OS: not provided
Scenario: Auditing service records to ensure consistent dollar rates are applied across identical addresses and service types without manual verification.
Observed behavior: The goal is to dynamically extract and count distinct rates per address and service type, returning a confirmation message when rates match and flagging when they do not.

Before you start

Ensure your dataset is organized in a tabular format with distinct columns for addresses, service types, and rates, and that you are using a spreadsheet software version that supports dynamic array functions.

Solution 1Recommended

Use Dynamic Array Functions to Verify Rate Consistency

Combine UNIQUE, FILTER, CHOOSECOLS, and COUNTA functions to extract and count the number of distinct rates per address.

Dynamic array functions provide a robust way to analyze complex conditions without VBA. By filtering data for matching addresses and service types, and isolating the distinct rates, you can quickly spot billing inconsistencies.

Set up the FILTER function

Use the FILTER function to isolate records matching a specific address and service type. For example: FILTER($A$2:$C$31, ($A$2:$A$31=A2)*($B$2:$B$31="On-site")).

Extract the rate column

Wrap the FILTER result in the CHOOSECOLS function to return only the column containing dollar rates. If your rate is in the third column, use CHOOSECOLS(..., 3).

Isolate distinct values

Enclose the formula in the UNIQUE function to remove any duplicate rate values for the matched address, leaving only distinct rates.

Count and output validation

Wrap the entire formula in COUNTA to return a number. For example: =COUNTA(CHOOSECOLS(UNIQUE(FILTER($A$2:$C$31,($A$2:$A$31=A2)*($B$2:$B$31="On-site"))),3)). You can combine this with an IF statement to return "Consistent" if the count equals 1, and "Inconsistent" otherwise.

Use Dynamic Array Functions to Verify Rate Consistency

Function Availability: Functions like CHOOSECOLS and UNIQUE are available in Microsoft 365, Excel 2021, and newer versions of WPS Office.

Solution 2

Highlight Inconsistent Rates Using Conditional Formatting

Apply conditional formatting based on the dynamic array formula to visually highlight rows with rate discrepancies.

Data Analysis in WPS Spreadsheet

Use WPS Spreadsheet to Compare and Analyze Rates Easily

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, FILTER, and CHOOSECOLS, empowering you to seamlessly verify billing consistencies and manage extensive datasets.

1. Open your data file: Launch WPS Spreadsheet and open the .xlsx file containing your service records.
2. Input the array formula: Select an empty cell next to the first address and enter the combined COUNTA, CHOOSECOLS, UNIQUE, and FILTER formula.
3. Apply to all rows: Press Enter, then drag the fill handle down to apply the verification logic across all addresses.
4. Review results: Check the output to instantly identify any addresses where the distinct rate count is greater than one.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Native support for modern dynamic array functions for complex data extraction.Built-in conditional formatting tools to quickly flag mismatched data visually.Free and lightweight alternative for fast and efficient data processing.

microsoft office alternative - wps office

Frequently Asked Questions

What if the FILTER function returns a #CALC! error?

If no matching addresses or service types are found in your dataset, FILTER will output a #CALC! error. To resolve this, provide a fallback value in the third argument of the FILTER function, such as "Not Found".

Can I use CHOOSECOLS in older spreadsheet versions?

No, CHOOSECOLS is a dynamic array function available in newer versions. If you are using an older spreadsheet software that doesn't support it, you can achieve a similar result using the INDEX function to extract a specific column.

How do I check multiple service types in the FILTER function?

To include multiple service types, use the addition symbol in the criteria argument. For example, (Range="On-site")+(Range="Remote") acts as an OR condition, allowing the FILTER function to return results matching either condition.

Excel & Data Problems