Practice 7: Nested Logical Decisions & Dual-Criteria Lookups

Key Topics & Core Concepts

Nested IF Structures: Evaluating tiered rating scales using multi-level nested IF statements.
Compound Logical Operators: Combining logical criteria using AND(...) and OR(...) logical wrappers.
Dual-Criteria Exact Lookups: Implementing robust XLOOKUP logic with multiple search arrays using the (array1=val1)*(array2=val2) syntax.
Error Handling: Wrapping formula calculations with IFERROR to display professional empty strings or custom alerts instead of #N/A.

Assignment Overview & Deliverables

In this advanced practice lab, you will expand the Courtyard Medical patient billing records with nested tier assignments, compound risk evaluations, and dual-criteria lookup formulas.

Starter Workbook: Download CourtyardMedical-02.xlsx (or continue with your completed file from Practice 6)
Completed File Name: [your initials] Practice07_Lookups.xlsx
Active Worksheets: Patient Billing, Fee Schedule, and Risk Summary

Step-by-Step Instructions

Step 1: Open Starter & Prepare Patient Billing Table

Open CourtyardMedical-02.xlsx and save it as [your initials] Practice07_Lookups.xlsx.
Click the Patient Billing worksheet tab.
Review columns A through G containing Patient ID, Name, Department, Copay, Total Charge, and Insurance Balance.

Step 2: Construct Nested IF Statements (Account Tier)

Click cell H4 (Account Tier).
Enter a nested IF formula based on Total Charges:
- Charges $\ge$ $2,000$ → "Tier 1"
- Charges $\ge$ $1,000$ → "Tier 2"
- Otherwise → "Standard"
Type: =IF(E4>=2000, "Tier 1", IF(E4>=1000, "Tier 2", "Standard")) and press Enter.
Copy the formula down through cell H30.

Step 3: Compound Logic with IF and AND / OR

Click cell I4 (Audit Review Flag).
Flag accounts requiring review if Insurance Balance is greater than $500 AND Account Tier is "Tier 1": =IF(AND(F4>500, H4="Tier 1"), "Audit Required", "Approved").
Copy the formula down through cell I30.

Step 4: Dual-Criteria XLOOKUP on Fee Schedule

Click the Fee Schedule tab to review the lookup table showing Department (Col A), Tier (Col B), and Base Fee (Col C).
Return to Patient Billing and click cell J4 (Standard Fee Lookup).
Type: =XLOOKUP(1, ('Fee Schedule'!$A$4:$A$20=C4)*('Fee Schedule'!$B$4:$B$20=H4), 'Fee Schedule'!$C$4:$C$20, "Not Found").
Copy down through cell J30 and verify each record retrieves its matching base fee without #N/A errors.

Step 5: Format, Align & Save Workbook

Format all fee and charge columns (E, F, J) as Currency ($#,##0.00).
Center-align Account Tier and Audit Review Flag columns (H, I).
Auto-fit all column widths to prevent text truncation.
Save your completed workbook.

Practice exercises are formative and prepare you for homework submissions. Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area	Verification Target
Nested IF Logic	Multi-tier conditional classifications configured without syntax errors.
Boolean AND/OR	Combined logical tests correctly structured inside condition wrapper.
Multi-Criteria Lookup	`XLOOKUP` logic matching Department and Tier simultaneously.
Error Shielding	Fallback default parameters applied cleanly without unhandled error codes.