Exact Key Lookups, Missing-Key Guards & Catalog Joins

Who this is for

Stage 3: Exact, Matrix & Bracket Lookups — Designed for learners mastering vlookup false, ifna & hlookup, xlookup leftward, reverse (-1) & multi-column spill, cross-sheet catalog joins & orphan audits, trim/value key hygiene, choose & duplicate gates through interactive spreadsheet examples.

What you will practise

• Pull exact catalog unit prices into G2:G4 with row-locked VLOOKUP(..., 3, FALSE) and compute the extended invoice total in G5.
• Replace the omitted-mode lookup logic by writing explicit exact-match VLOOKUP(..., 2, FALSE) formulas in E2:E4 on the unsorted parts list and sum E2:E4 in E5.
• Wrap exact VLOOKUP in IFNA(..., 0) across E2:E5 so unlisted SKUs evaluate to 0, then count how many quote lines had unlisted SKUs in E6.
• Pull Standard freight rates (row 2 of $B$1:$E$3) into C6:C8 and Express freight rates (row 3 of $B$1:$E$3) into D6:D8 using exact HLOOKUP.
• Retrieve vendor payment terms into F2:F5 using XLOOKUP with "Pending Onboarding" as the 4th argument, and count pending vendors in F6.
• Map each imported expense description in E2:E4 back to its GL Account Code in $A$2:$A$6 by searching $C$2:$C$6 with XLOOKUP and fallback "Unmapped".
• Pull each chip's initial price into F2:F4 (top-down XLOOKUP) and its latest revised price into G2:G4 using XLOOKUP with search_mode = -1.
• Spill Product Name, Category, and Unit Price across H2:J4 by writing multi-column XLOOKUP formulas in H2:H4, then compute Line Totals in K2:K4.

Your learning path

1. XLOOKUP function: exact match, left lookup, and missing-key fallback

   Look up values in any column direction without counting column numbers, return a clear fallback when a code is missing, and optionally scan bottom-up for the latest entry.

2. Reverse Left Lookup: Retrieve Values to the Left of the Lookup Column

   Look up an ID or SKU positioned to the left of the search column without rearranging worksheet columns using XLOOKUP or INDEX and MATCH.

3. Cascading Lookup Across 2 Lookup Tables (XLOOKUP if_not_found & IFNA)

   Search a Primary Domestic Catalog first and automatically fall back to a Secondary International/Archive Catalog when missing using XLOOKUP's 4th [if_not_found] argument.

4. Find the Most Common Text Values (#1 Categorical Mode & Ranked Runner-Ups)

   Extract the most frequent text label and second-most common category—where numeric MODE returns #N/A—using COUNTIF frequencies, MAX, MATCH, INDEX, and FILTER.

5. Check If a Value Exists in a Range (COUNTIF & XMATCH Membership Test)

   Check a nonblank ID against an approved list, distinguish literal matching from COUNTIF criteria, and test whether either of two watch-list IDs is present.

6. HLOOKUP function: look up values across horizontal column headers

   Search the top row of a wide quarterly table for an exact period label and return the value from a specified row beneath it.

7. Match First Row Not Containing Specific Text (XLOOKUP & SEARCH)

   Retrieve the first order or server record that excludes an internal/test substring using an ISERROR(SEARCH(...)) helper flag with XLOOKUP and count all non-matching records with COUNTIF.

8. Horizontal XLOOKUP Across Columns: Upward Return & Right-to-Left Last Match

   Replace fragile row-index HLOOKUP formulas with horizontal XLOOKUP across column headers, including upward lookups into rows above the match row and right-to-left latest-revision searches.