The right product but the wrong month
Use the month header position as the third INDEX argument. A fixed column number will not survive reordered headers.
Find a value at a row and column intersection
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
In G5, retrieve net units for the product in G2 and month in G3. Match text without regard to case, use the first occurrence of a repeated label, and return Not found if either label is absent. Preserve zero and negative results.
Follow the highlighted cells. Switch to Your turn to edit. Answer cell: G5. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Use two labels to retrieve net units from a small matrix, even after rows and month columns move.
THE QUICK ANSWER01
One XMATCH locates the product row and another locates the month column. INDEX retrieves their intersection. IFNA supplies the missing-label message for #N/A and leaves other calculation errors visible.
ƒx
=IFNA(INDEX(B2:D5,XMATCH(G2,A2:A5,0),XMATCH(G3,B1:D1,0)),"Not found")For the example data
G5 18
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Two positions describe one cell
Each XMATCH returns a position within its own lookup range. Those positions must correspond to the rows and columns of the INDEX rectangle. Moving the lookup labels without moving their associated numeric values would change the data itself.
First match and missing labels
Repeated labels select the first matching row and first matching column. Spaces remain significant. This exercise requires nonempty text labels and numeric matrix values; blanks, text observations and booleans in the matrix are outside its supported inputs.
Reordering is part of the task
The variation datasets move both products and month headers. A formula hard-coded to a row or month number can succeed on the first table and fail the full check.
Read the full step-by-step walkthrough
- Separate labels from values
A2:A5 names the rows; B1:D1 names the columns. B2:D5 contains the numeric values. Keep the two position searches relative to this same rectangle.
- Locate the product row
XMATCH returns a one-based position in A2:A5. Exact mode 0 treats the query as a literal label. The first matching spelling wins, regardless of letter case.
- Locate the month column
Search the header row independently. A month that moves from the second column to the third should still be found by its label.
- Read the intersection
Pass the row position and column position to INDEX. A zero result is a real value. Negative net units can represent returns and stay negative.
- Test either missing label
Try a product or month outside the table. IFNA converts only #N/A to Not found. Other errors, including an unrecognized function or an invalid reference, remain visible for diagnosis.
Things to watch
Zero is shown as missing
Do not use IF(result,...). Zero and negative numbers are valid results in this table.
The row count is off by one
Search A2:A5 for the row position, not A1:A5. The numeric rectangle also starts on row 2.
A calculation error is not a missing label
Not found means that XMATCH returned #N/A. A #NAME? or #REF! result needs investigation. Replacing IFNA with IFERROR can hide an unsupported function or broken reference.
About this example
Browser checks cover the supplied task rules, datasets, grading and downloads. On October 17, 2026, this Mac Excel opened the workbook without a repair prompt but did not recognize XMATCH; native recalculation has not passed. Excel editing and saving are unavailable until activation. Google Sheets remains unverified.
Content version 2. Practice checks compare calculated results across different datasets; they do not certify every possible formula.