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
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
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.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G6, G7. Browser calculations may differ from your Excel version.
Matching rows
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.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Match three fields on the same row, return the first rate, and count complete matches before treating the key as unique.
THE QUICK ANSWER01
EXACT compares each lowercased source field with its query. Multiplication creates a 1 only where all three comparisons are true on one row. XLOOKUP returns the first rate at a 1; a separate count reveals repeated complete keys.
G6
=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)G7
=SUMPRODUCT(EXACT(LOWER(A2:A9),LOWER(G2))*EXACT(LOWER(B2:B9),LOWER(G3))*EXACT(LOWER(C2:C9),LOWER(G4)))For the example data
G6 44G7 1
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How 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.