Only one or two fields are checked
A matching product in another region or tier is a decoy. Require all three fields on the same source row.
Look up a rate with multiple criteria
About this lesson
Match three fields on the same row, return the first rate, and count complete matches before treating the key as unique.
Task requirements
Return in G6 the numeric rate from the first source row whose Product, Region and Tier all match G2:G4, after lowercasing each text key. Keep spaces and punctuation exactly as stored. Return Not found if no complete row matches. In G7, count all complete matches, including a first rate of zero or a refund.
Teaching · read-only worksheet ·
Step 1 / 8Define one complete match
More
Inspect the highlighted cells. This step has no automatic change to apply. Teaching is read-only; switch to Your turn to edit.
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G6, G7. Browser calculations may differ from your Excel version.
Use INDEX and MATCH for the same lookup
MATCH finds the first 1 in the same complete-match mask. INDEX reads the rate from that position; IFNA returns the missing-result text. Keep the matching-count formula in G7.
Copy into G6 when you want to try this method. Your worksheet is unchanged by opening or copying it. Desktop array entry and recalculation remain version-dependent and unverified here.
Practice stays on this device. No worksheet data is uploaded.
Choose the result you need
Multiply the three same-row comparisons to make a mask of 1s and 0s. MATCH with exact mode 0 finds the first 1; INDEX reads the rate at that position. Aster/North/Basic returns 44 in G6, while SUMPRODUCT counts one complete match in G7. IFNA supplies text only when no mask entry matches.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
All result formulas
- G6
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")- G7
=SUMPRODUCT(EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)))
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "Aster" | "North" | "Basic" | 44 | "Product" | "Aster" |
| 3 | "Aster" | "South" | "Basic" | 49 | "Region" | "North" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "Basic" |
| 5 | "Birch" | "North" | "Basic" | 28 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | 44 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 1 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
These are reference examples with matching downloads. The primary worksheet has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.
Open the primary practiceMicrosoft reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.
All worked examples
Expand an example to read its complete source data and checked results, then download the matching workbook. Cell references belong to that example. Opening it leaves the primary practice and your progress unchanged.
INDEX and MATCH
Multiply the three same-row comparisons to make a mask of 1s and 0s. MATCH with exact mode 0 finds the first 1; INDEX reads the rate at that position. Aster/North/Basic returns 44 in G6, while SUMPRODUCT counts one complete match in G7. IFNA supplies text only when no mask entry matches.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "Aster" | "North" | "Basic" | 44 | "Product" | "Aster" |
| 3 | "Aster" | "South" | "Basic" | 49 | "Region" | "North" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "Basic" |
| 5 | "Birch" | "North" | "Basic" | 28 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | 44 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 1 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- 44
- G7
- 1
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookCompare with XLOOKUP
XLOOKUP searches for 1 in the same mask and reads the aligned rate directly. These identical source cells again return 44 and one matching row. This changes the reference method, not the primary worksheet. XLOOKUP is unavailable in Excel 2016 and Excel 2019.
=XLOOKUP(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),D2:D9,"Not found",0)Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "Aster" | "North" | "Basic" | 44 | "Product" | "Aster" |
| 3 | "Aster" | "South" | "Basic" | 49 | "Region" | "North" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "Basic" |
| 5 | "Birch" | "North" | "Basic" | 28 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | 44 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 1 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- 44
- G7
- 1
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookKeep the first match
Two source rows match all three lowercased fields. INDEX/MATCH returns 44 from the first, and G7 reports 2. The later rate of 81 is not added. First means source order, not latest date or largest rate.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "Aster" | "North" | "Basic" | 44 | "Product" | "aster" |
| 3 | "Aster" | "South" | "Basic" | 49 | "Region" | "NORTH" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "basic" |
| 5 | "ASTER" | "north" | "BASIC" | 81 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | 44 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 2 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- 44
- G7
- 2
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookDetect no complete match
Aster, West and Pro each appear somewhere, but never together on one source row. There is no 1 in the combined mask: IFNA returns the text Not found and G7 is numeric 0. The missing tuple must not borrow fields from different rows.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "Aster" | "North" | "Basic" | 44 | "Product" | "Aster" |
| 3 | "Aster" | "South" | "Basic" | 49 | "Region" | "West" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "Pro" |
| 5 | "Birch" | "North" | "Basic" | 28 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | "Not found" |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 0 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- "Not found"
- G7
- 0
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookReturn a zero rate
The first complete row has rate 0 and another complete match follows it. G6 remains numeric 0 and G7 is 2. Counting nonzero rates would lose a valid match; replacing zero with Not found would confuse a result with a missing key.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "Aster" | "North" | "Basic" | 0 | "Product" | "Aster" |
| 3 | "Aster" | "South" | "Basic" | 49 | "Region" | "North" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "Basic" |
| 5 | "Aster" | "North" | "Basic" | 25 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | 0 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 2 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- 0
- G7
- 2
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookKeep a negative rate
The complete source row returns -12.5 with a count of 1. A negative adjustment remains a numeric value. This lookup selects one rate; it does not sum rates or reject the sign of a valid numeric result.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "Aster" | "North" | "Basic" | -12.5 | "Product" | "Aster" |
| 3 | "Aster" | "South" | "Basic" | 49 | "Region" | "North" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "Basic" |
| 5 | "Birch" | "North" | "Basic" | 28 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | -12.5 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 1 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- -12.5
- G7
- 1
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookKeep fields separate
The tuples (ab, c, d) and (a, bc, d) both become abcd when joined. The query asks for (a, bc, d), so separate comparisons select the second source row: 27 and one match. An unseparated joined-key lookup would incorrectly return 11.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "ab" | "c" | "d" | 11 | "Product" | "a" |
| 3 | "a" | "bc" | "d" | 27 | "Region" | "bc" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "d" |
| 5 | "Birch" | "North" | "Basic" | 28 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | 27 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 1 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- 27
- G7
- 1
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookKeep leading zeros in text IDs
The product keys are stored as text. The query 007 must select the second source row: G6 is 52 and G7 is 1. Text 7 in the first row is a different identifier with rate 7. Converting both keys to numbers would discard the leading zeros and change the matching policy.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "7" | "North" | "Basic" | 7 | "Product" | "007" |
| 3 | "007" | "North" | "Basic" | 52 | "Region" | "North" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "Basic" |
| 5 | "Birch" | "North" | "Basic" | 28 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | 52 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 1 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- 52
- G7
- 1
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookKeep spaces in the stored labels
The query region is North with no trailing space. The first source row stores North followed by a space and has rate 9; the second stores North exactly and has rate 21. The complete result is 21 and one match. Trimming the stored labels would create a different key policy and include the first row too.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "Aster" | "North " | "Basic" | 9 | "Product" | "Aster" |
| 3 | "Aster" | "North" | "Basic" | 21 | "Region" | "North" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "Basic" |
| 5 | "Birch" | "North" | "Basic" | 28 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | 21 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 1 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- 21
- G7
- 1
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookMatch punctuation as literal text
The requested product Kit*, region Zone? and tier Basic~ are literal labels. The KitXL/ZoneA row is a decoy, not a match for these stored characters. Comparing the separate fields with EXACT after LOWER returns rate 58 and one matching row. The mask lookup searches for numeric 1 with MATCH mode 0; the label punctuation is not a search pattern.
=IFNA(INDEX(D2:D9,MATCH(1,EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)),0)),"Not found")Enter in G6 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Product" | "Region" | "Tier" | "Rate" | "Query controls" | — |
| 2 | "KitXL" | "ZoneA" | "Basic~" | 2 | "Product" | "Kit*" |
| 3 | "Kit*" | "Zone?" | "Basic~" | 58 | "Region" | "Zone?" |
| 4 | "Aster" | "North" | "Pro" | 65 | "Tier" | "Basic~" |
| 5 | "Birch" | "North" | "Basic" | 28 | — | — |
| 6 | "Cedar" | "West" | "Basic" | 30 | "First rate" | 58 |
| 7 | "Dune" | "East" | "Pro" | 90 | "Matching rows" | 1 |
| 8 | "Elm" | "South" | "Pro" | 35 | — | — |
| 9 | "Fir" | "West" | "Pro" | 64 | — | — |
- G6
- 58
- G7
- 1
Keys are nonempty text. LOWER ignores case; EXACT preserves stored spaces, punctuation and field boundaries. Keep the source ranges aligned. Enter G6 and G7 separately. Desktop array entry and recalculation depend on the application/version and have not been verified for these files.
Download this example's workbookHow it works
First match and unique match are different decisions
Two complete rows can have different rates. This task returns the first in source order and counts both. Reordering those rows can change the returned rate while the count stays 2. Decide the duplicate policy before using a rate table.
Keep fields separate
The tuples (ab, c, d) and (a, bc, d) both become abcd when joined without separators. A separator can also appear inside a stored label. Separate comparisons keep these tuples distinct without inventing a parsing convention.
Compare exact lowercased text
Keys are nonempty text. Lowercasing ignores case, while EXACT keeps spaces, punctuation and different Unicode spellings distinct. Text 007 and text 7 are different labels. Store identifier keys as text; correct blank/numeric keys or nonnumeric rates before checking.
Use INDEX and MATCH on the same mask
The alternative method panel supplies a complete INDEX/MATCH formula for G6. MATCH locates the first 1 in the same three-field mask and INDEX reads that rate. Keep the SUMPRODUCT formula in G7. Both methods solve this task on the same page.
Choose a supported Excel version
XLOOKUP is unavailable in Excel 2016 and Excel 2019. The browser checks both methods on these original cases. Array-formula entry and recalculation in a desktop version or Google Sheets are separate compatibility checks; those workbooks remain labelled pending.
Read the full step-by-step walkthrough
- Define one complete match
One source row must satisfy Product, Region and Tier together. Start with the eight candidate rows; matching each field somewhere in the table is not enough.
- Compare the product labels
LOWER removes letter-case differences. EXACT then compares the stored characters, keeping spaces, punctuation and leading zeros meaningful.
- Keep the matching region on the same row
The region comparison must be true beside the matching product. The row panel shows which candidates remain after both conditions.
- Apply the tier condition
A product can have several tiers in one region. The third comparison keeps the requested tier without borrowing a match from another row.
- Turn complete matches into ones
Multiplication treats TRUE as 1 and FALSE as 0. Only a row with three true comparisons produces 1. Keep the three fields separate to avoid joined-key collisions.
- Return the first complete rate
Search for 1 in the mask and return the rate from D2:D9 on that same row. The normal search order takes the first complete match, even when its rate is zero.
- Distinguish a missing tuple from a zero rate
The missing-result argument returns Not found. A numeric zero or negative rate remains numeric. Exact mode 0 searches for the mask value 1.
- Count complete matches
SUMPRODUCT adds the same 1/0 mask in G7. A count above 1 explains why returning one rate does not establish that the source key is unique.
Things to watch
Duplicate rates are added
SUMIFS answers a total question. The rate task selects the first complete row and reports its match count separately.
Labels are joined into an ambiguous key
Different field boundaries can produce identical joined text. Compare each declared field independently.
A zero rate disappears from the count
Count complete key matches, not positive or nonzero rates. Zero is a valid numeric result.
About this example
Browser status applies to supplied original datasets, complete value/count grading and local downloads. Native Excel/Google Sheets recalculation and array-entry behavior remain unverified. XLOOKUP requires a supporting version.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.