Practice 18: Dynamic Matrix Lookups (INDEX & XMATCH)

Key Topics & Core Concepts

Two-Way Matrix Retrieval: Nesting dynamic positional finders inside INDEX(array, row_num, [col_num]) to retrieve values at two-dimensional intersections.
Positional Finders with XMATCH: Using modern XMATCH(lookup_value, lookup_array) defaulting to exact match without legacy mode flags.
Flexible Table Intersections: Querying pricing and seasonal rate matrices across varying property types and seasonal calendar tiers.
Formula Error Shielding: Handling invalid search queries gracefully with IFERROR wrappers.

Assignment Overview & Deliverables

In this practice lab, you will configure dynamic matrix lookup models using INDEX nested with dual XMATCH functions to automate rate queries for Perfect Vacation Rentals.

Starter Workbook: Download PerfectRentals-06.xlsx (or continue with your completed file from Practice 17)
Completed File Name: [your initials] Practice18_IndexXmatch.xlsx
Active Worksheets: Matrix Rates and Rate Lookup

Step-by-Step Instructions

Step 1: Open Starter & Review Rate Matrix

Download and open PerfectRentals-06.xlsx in Excel (or continue from Practice 17).
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice18_IndexXmatch.xlsx.
Click the Matrix Rates tab.
Inspect the two-dimensional rate grid in range B4:F10, where rows list Property Types (cells A4:A10) and columns list Seasons (cells B3:F3: Spring, Peak Summer, Late Summer, Autumn, Winter).

Step 2: Determine Relative Row Position with XMATCH

Click the Rate Lookup tab.
Review the query parameters in cells B4 (Target Property Type, e.g., "Cabin") and B5 (Target Season, e.g., "Peak Summer").
In cell B7 (Row Index Finder), type: =XMATCH(B4, 'Matrix Rates'!$A$4:$A$10) and press Enter.
Verify that XMATCH returns the integer index corresponding to the target property row.

Step 3: Determine Relative Column Position with XMATCH

In cell B8 (Column Index Finder), type: =XMATCH(B5, 'Matrix Rates'!$B$3:$F$3) and press Enter.
Verify that XMATCH returns the correct horizontal position index for the selected season.

Step 4: Combine into a Two-Way INDEX / XMATCH Formula

Click cell B10 (Dynamic Nightly Rate).
Enter the nested two-way lookup formula: =INDEX('Matrix Rates'!$B$4:$F$10, XMATCH(B4, 'Matrix Rates'!$A$4:$A$10), XMATCH(B5, 'Matrix Rates'!$B$3:$F$3))
Press Enter.
Test the dynamic lookup by changing cell B4 to "Lakeside Villa" and B5 to "Autumn" to confirm that the intersection rate updates accurately.

Step 5: Formatting, Error Shielding & Save Workbook

Format cell B10 as Currency ($#,##0.00).
Wrap the lookup in cell B10 with IFERROR: =IFERROR(INDEX('Matrix Rates'!$B$4:$F$10, XMATCH(B4, 'Matrix Rates'!$A$4:$A$10), XMATCH(B5, 'Matrix Rates'!$B$3:$F$3)), "Invalid Selection")
Auto-fit columns to eliminate 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
XMATCH Functionality	Positional index calculations referencing vertical and horizontal headers.
Two-Way Matrix Retrieval	`=INDEX(..., XMATCH(...), XMATCH(...))` nested syntax returning exact intersection value.
Error Handling	`IFERROR` shield preventing unhandled `#N/A` alerts.
Presentation Polish	Clean currency formatting without column clipping or visual defects.