Module 04: Connect and summarize data
Build an exact lookup with INDEX and MATCH
Find a key's position, return its corresponding value, and keep the method compatible with Excel 2016 and 2019.
Excel 2016, 2019, 2021, 2024, and Microsoft 365 for Windows desktop. These single-result formulas use ordinary Enter, not Ctrl+Shift+Enter. English formulas use commas; some locales require semicolons. Time is an estimate.
Useful first: Copy formulas while keeping shared inputs fixed · Clean hidden spaces without losing the original text
- Explain a match position relative to its search range.
- Return a value from a different column with INDEX.
- Use exact-match mode and handle only the expected missing-key error.
Small data. A result you can check.
Synthetic data only. Paste at A1; create helper and final formulas separately.
View the raw practice data
Name ProductID Price Blue Mug P101 12 Tray P102 8 Cup P103 5
01Arrange the catalog with the return value on the left
Paste the sample into A1:C4. Here Name is in A, ProductID in B, and Price in C. Enter ProductID in E1 and P102 in E2. This arrangement emphasizes that INDEX can return a name from the left of the key column; the return range and search range do not need to be in a particular left-to-right order.
In F1 enter Position, in G1 enter Name, and in H1 enter Price. Keep the helper results visible while learning. Breaking the lookup into two smaller formulas makes a wrong range or unexpected match easier to diagnose.
| Position within B2:B4 | Worksheet cell | Key |
|---|---|---|
| 1 | B2 | P101 |
| 2 | B3 | P102 |
| 3 | B4 | P103 |
02Find the relative position with MATCH
In F2 enter =MATCH(E2,$B$2:$B$4,0). It returns 2: P102 is the second item inside B2:B4. It does not return worksheet row number 3. That relative position is exactly what the next function needs.
Always include the final 0 for this exact-key exercise. Omitting match_type defaults to an approximate mode that assumes an ordered search range. MATCH's text comparison is not case-sensitive, and its exact mode can still interpret * and ? as wildcards. The sample's alphanumeric IDs avoid those special characters; literal wildcard-bearing IDs need a separate escaping policy.
=MATCH(E2,$B$2:$B$4,0)Enter in F2. Exact P102 produces position 2 without sorting the catalog.
03Use that position in an aligned return range
Enter =INDEX($A$2:$A$4,F2) in G2 and =INDEX($C$2:$C$4,F2) in H2. They return Tray and 8 because both return ranges begin at the same source row as the search range. A shifted return range would retrieve information from the wrong record even if MATCH found the correct key.
For a compact final formula, enter the expression below in G5. IFNA substitutes a readable result only for #N/A. It does not intentionally hide every error as IFERROR would. When a key is missing, still inspect spelling, spaces, and source completeness rather than assuming the catalog should contain it.
=IFNA(INDEX($A$2:$A$4,MATCH(E2,$B$2:$B$4,0)),"Not found")Enter in G5, away from the helper results. This returns one name and works without dynamic arrays.
Reorder complete source records and check whether the returned product remains correct.
- On a copy, reorder A2:C4 as Cup/P103/5, Blue Mug/P101/12, then Tray/P102/8. Move each complete row together.
- Keep E2 at P102 and inspect F2, G2, H2, and G5.
- Change E2 to P999 to exercise the missing-key path, then restore P102.
Show the worked answer
After reordering, F2 is 3, while G2 and G5 remain Tray and H2 remains 8.
For P999, the helper chain shows #N/A and the final formula in G5 shows Not found.
The three-record catalog has the same ID/name/price relationships after reordering.
Check your work- Search and return ranges remain B2:B4, A2:A4, and C2:C4.
- MATCH uses 0 explicitly.
- No source column was sorted independently of the other two.
One question before you move on.
Ready for the next step?
Mark this lesson when you can explain the idea and reproduce the practice result.
Your checklist stays on this browser.Reference notes
These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.
Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.