Build an Excel Formula for Billing Rules with IF and OR
Question details
Create a formula that applies billing conditions in order and returns Billable, Unbillable, or a lookup result.

- 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.
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.
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.
In Excel, select the cell where you want the billing result for row 20.
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.
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.
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 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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 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. 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. 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.

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.




