Formula Errors

Build an Excel Formula for Billing Rules with IF and OR

Ayan MasoodAyan Masood Nov 18, 2026 869 views

Question details

Create a formula that applies billing conditions in order and returns Billable, Unbillable, or a lookup result.

Build an Excel Formula for Billing Rules with IF and OR
Product
Microsoft Excel
Device & OS
not provided
Scenario
Evaluate values in columns AG, AF, AB, and AD against billing rules and an excluded-payer lookup table.
Observed behavior
The formula should return Billable first, then evaluate the Unbillable conditions, and otherwise return a lookup result or the value in AB.
Before you start

Check that the source values are in columns AG, AF, AB, and AD, and that the sheet named Excluded Payers has lookup keys in column A and results in column C. The formula below uses row 20; adjust the row references if your data starts elsewhere.

Solution 1Recommended

Apply the Billing Rules with Nested IF and VLOOKUP

Use this formula to evaluate the rules in priority order and fall back to AB20 if the lookup returns an error.

1
Select the result cell

In Excel, select the cell where you want the billing result for row 20.

2
Enter the formula

Enter =IF(AG20="Billable",AG20,IF(OR(AG20="Not Billable",AND(AF20="Unspecified Coverage",AB20<>"Weak Match")),"Unbillable",IFERROR(VLOOKUP(AD20,'Excluded Payers'!A:C,3,FALSE),AB20))) and press Enter.

3
Check the rule order

Review the result: AG20 equal to Billable takes priority. Otherwise, AG20 equal to Not Billable, or AF20 equal to Unspecified Coverage while AB20 is not Weak Match, returns Unbillable.

4
Fill down

Drag the result cell's fill handle down to apply the formula to other rows. When neither billing condition applies, VLOOKUP searches column A of Excluded Payers for AD20 and returns column C; IFERROR returns AB20 if the lookup produces an error.

Apply the Billing Rules with Nested IF and VLOOKUP
Check the input labels: The input label Not Billable returns the output Unbillable. If your Excel regional settings use semicolons as argument separators, replace the formula's commas with semicolons.
Work with billing formulas in WPS

Apply the Same Billing Rules in WPS Spreadsheet

Use WPS Spreadsheet to enter the nested IF and VLOOKUP formula, check its results, and fill it down through your billing data.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Check the lookup sheet: Select the Excluded Payers sheet tab and confirm that payer keys are in column A and lookup results are in column C.
  3. 3. Enter the formula: Select the result cell for row 20 and enter =IF(AG20="Billable",AG20,IF(OR(AG20="Not Billable",AND(AF20="Unspecified Coverage",AB20<>"Weak Match")),"Unbillable",IFERROR(VLOOKUP(AD20,'Excluded Payers'!A:C,3,FALSE),AB20))).
  4. 4. Apply it to other rows: Press Enter, check the result, and drag the cell's fill handle down to apply the formula to the remaining rows.
Apply ordered billing rules with familiar spreadsheet functionsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceGet started with the free version of WPS Office
microsoft office alternative - wps office

Frequently Asked Questions

Which rule takes priority if AG20 is Billable?

The first IF returns Billable immediately, even if the AF20 and AB20 values would otherwise meet the Unbillable condition.

When does the formula return Unbillable?

It returns Unbillable when AG20 is Not Billable, or when AF20 is Unspecified Coverage and AB20 is not Weak Match, provided AG20 is not Billable.

What happens if the payer is not found?

VLOOKUP searches for AD20 in column A of the Excluded Payers sheet using an exact match. If the lookup returns an error, IFERROR returns the value in AB20.

Why is Excel rejecting the formula?

Check that the sheet is named Excluded Payers and that the formula uses the correct argument separator. Some regional settings require semicolons instead of commas.