PUNGGOL TUITION · PRACTICAL MASTERY GUIDE

A learner can find the right name in a list and still hesitate over the number returned by XMATCH. The difficulty is usually not typing the function. It is deciding whether the task needs a relative position, an exact or approximate match, and a first-to-last, reverse or binary search.

XMATCH searches a one-dimensional array and returns the matched item’s relative position. Exact matching and first-to-last search are the defaults. Optional match_mode values control exact, next smaller, next larger and wildcard matching; search_mode values control direction and binary search. Binary modes require correctly sorted arrays.

This guide treats Excel XMATCH as a reasoning skill. It begins with the governing model, moves through worked examples and diagnostic routes, and ends with transfer cases, practice with explained answers, parent decisions and FAQs. The examples are proposed home or lesson activities; they are not claims about a physical Punggol branch, school affiliation, timetable, class size, fee or guaranteed result.

For a Punggol family, the practical aim is simple: a learner should be able to use a short study block before schoolwork, after CCA, or during a weekend library session without needing a large project. Each chapter is designed so the smallest useful example can be completed, discussed and saved as a test fixture.

The official technical reference for this article is Official Microsoft Support documentation for XMATCH. Where the feature is version-sensitive or still developing, the article states that boundary explicitly. Examples were independently checked against the runtime or specification named in the reference section.

Choose a reading route

If the immediate concern is “I can copy the syntax but I cannot explain the result”, use the first route. If a real project is already failing, use the diagnostic route. Parents can use the final route to decide whether more practice, a smaller prerequisite or a different example is needed. For the wider learning framework, see How Studying Works.

Start from the model: Chapters 1–4. Begin with Return a relative position and continue in order to Find the last duplicate.

Diagnose a confusing result: Chapters 5–8. Begin with Use wildcard matching deliberately and continue in order to Request the next larger item.

Build reliable examples: Chapters 9–12. Begin with Use ascending binary search only on sorted data and continue in order to Treat text case as equivalent.

Transfer to real work: Chapters 13–16. Begin with Diagnose number stored as text and continue in order to Perform a two-way lookup.

Decide and teach: Chapters 17–20. Begin with Match multiple conditions with a Boolean array and continue in order to Choose XMATCH for positions.

Open the full 20-chapter index

1. 1. Return a relative position
2. 2. Use exact match by default
3. 3. Find the first duplicate
4. 4. Find the last duplicate
5. 5. Use wildcard matching deliberately
6. 6. Escape a literal wildcard
7. 7. Request the next smaller item
8. 8. Request the next larger item
9. 9. Use ascending binary search only on sorted data
10. 10. Use descending binary search only on sorted data
11. 11. Search a horizontal array
12. 12. Treat text case as equivalent
13. 13. Diagnose number stored as text
14. 14. Handle not found with IFNA
15. 15. Return a value with INDEX and XMATCH
16. 16. Perform a two-way lookup
17. 17. Match multiple conditions with a Boolean array
18. 18. Use a spilled lookup array
19. 19. Check version compatibility
20. 20. Choose XMATCH for positions

CHAPTER 1 OF 20 · Back to contents

1. Return a relative position

The short answer: Return a relative position is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: XMATCH returns the relative position of the matched item within lookup_array, not the item itself.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “XMATCH returns the relative position of the matched item within lookup_array, not the item itself.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the return a relative position example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A5 = {Blue;Gold;Green;Red}
=XMATCH("Green",A2:A5)

Expected result and reasoning. The result is 3 because Green is the third item in A2:A5. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Expecting the text Green confuses a position function with a value-returning lookup. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Is the requested output a position or the matched cell value?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for return a relative position
• Variable to hold constant: the surrounding setup, so the comparison isolates return a relative position.
• Variable to change: change Green to Blue and predict position 1
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For return a relative position, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a CCA attendance export. Transfer return a relative position into a CCA attendance export. Preserve the governing rule—XMATCH returns the relative position of the matched item within lookup_array, not the item itself.—while identifying which cells, dimensions or error states change. Return a relative position is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH returns the relative position of the matched item within lookup_array, not the item itself.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a library-book record. Transfer return a relative position into a library-book record. Preserve the governing rule—XMATCH returns the relative position of the matched item within lookup_array, not the item itself.—while identifying which cells, dimensions or error states change. Return a relative position is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH returns the relative position of the matched item within lookup_array, not the item itself.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a school-event roster. Transfer return a relative position into a school-event roster. Preserve the governing rule—XMATCH returns the relative position of the matched item within lookup_array, not the item itself.—while identifying which cells, dimensions or error states change. Return a relative position is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH returns the relative position of the matched item within lookup_array, not the item itself.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a subject-planning workbook. Transfer return a relative position into a subject-planning workbook. Preserve the governing rule—XMATCH returns the relative position of the matched item within lookup_array, not the item itself.—while identifying which cells, dimensions or error states change. Return a relative position is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH returns the relative position of the matched item within lookup_array, not the item itself.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for return a relative position, predict the returned value or array, then change Green to Blue and predict position 1. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 3 because Green is the third item in A2:A5. The contrast follows from this rule: XMATCH returns the relative position of the matched item within lookup_array, not the item itself. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for return a relative position, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain return a relative position, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Contents · Full contents · Next: Use exact match by default →

CHAPTER 2 OF 20 · Back to contents

2. Use exact match by default

The short answer: Use exact match by default is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: When match_mode is omitted, XMATCH uses exact match mode 0.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “When match_mode is omitted, XMATCH uses exact match mode 0.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the use exact match by default example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A5 = {10;20;30;40}
=XMATCH(25,A2:A5)

Expected result and reasoning. The result is #N/A because 25 is absent and the default requires an exact match. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Assuming an omitted option means approximate matching imports MATCH habits into XMATCH. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Was match_mode omitted, and what is XMATCH’s documented default?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for use exact match by default
• Variable to hold constant: the surrounding setup, so the comparison isolates use exact match by default.
• Variable to change: change the lookup value to 30
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For use exact match by default, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a revision-topic list. Transfer use exact match by default into a revision-topic list. Preserve the governing rule—When match_mode is omitted, XMATCH uses exact match mode 0.—while identifying which cells, dimensions or error states change. Use exact match by default is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “When match_mode is omitted, XMATCH uses exact match mode 0.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a household budget sheet. Transfer use exact match by default into a household budget sheet. Preserve the governing rule—When match_mode is omitted, XMATCH uses exact match mode 0.—while identifying which cells, dimensions or error states change. Use exact match by default is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “When match_mode is omitted, XMATCH uses exact match mode 0.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a survey response table. Transfer use exact match by default into a survey response table. Preserve the governing rule—When match_mode is omitted, XMATCH uses exact match mode 0.—while identifying which cells, dimensions or error states change. Use exact match by default is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “When match_mode is omitted, XMATCH uses exact match mode 0.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a data-cleaning checklist. Transfer use exact match by default into a data-cleaning checklist. Preserve the governing rule—When match_mode is omitted, XMATCH uses exact match mode 0.—while identifying which cells, dimensions or error states change. Use exact match by default is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “When match_mode is omitted, XMATCH uses exact match mode 0.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for use exact match by default, predict the returned value or array, then change the lookup value to 30. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is #N/A because 25 is absent and the default requires an exact match. The contrast follows from this rule: When match_mode is omitted, XMATCH uses exact match mode 0. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for use exact match by default, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain use exact match by default, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Return a relative position · Full contents · Next: Find the first duplicate →

CHAPTER 3 OF 20 · Back to contents

3. Find the first duplicate

The short answer: Find the first duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the find the first duplicate example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A6 = {Ada;Ben;Ada;Chen;Ada}
=XMATCH("Ada",A2:A6)

Expected result and reasoning. The result is 1. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Reporting 5 because it is the latest record ignores the default search direction. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which direction did the search use?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for find the first duplicate
• Variable to hold constant: the surrounding setup, so the comparison isolates find the first duplicate.
• Variable to change: supply search_mode -1 and compare
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For find the first duplicate, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a library-book record. Transfer find the first duplicate into a library-book record. Preserve the governing rule—The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.—while identifying which cells, dimensions or error states change. Find the first duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a school-event roster. Transfer find the first duplicate into a school-event roster. Preserve the governing rule—The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.—while identifying which cells, dimensions or error states change. Find the first duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a subject-planning workbook. Transfer find the first duplicate into a subject-planning workbook. Preserve the governing rule—The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.—while identifying which cells, dimensions or error states change. Find the first duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a CCA attendance export. Transfer find the first duplicate into a CCA attendance export. Preserve the governing rule—The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.—while identifying which cells, dimensions or error states change. Find the first duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for find the first duplicate, predict the returned value or array, then supply search_mode -1 and compare. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 1. The contrast follows from this rule: The default search_mode 1 scans first-to-last, so an exact duplicate returns its first relative position. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for find the first duplicate, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain find the first duplicate, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Use exact match by default · Full contents · Next: Find the last duplicate →

CHAPTER 4 OF 20 · Back to contents

4. Find the last duplicate

The short answer: Find the last duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the find the last duplicate example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A6 = {Ada;Ben;Ada;Chen;Ada}
=XMATCH("Ada",A2:A6,0,-1)

Expected result and reasoning. The result is 5. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Changing search direction does not change the meaning of the returned position. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Is the fifth array element or worksheet row 5 being reported?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for find the last duplicate
• Variable to hold constant: the surrounding setup, so the comparison isolates find the last duplicate.
• Variable to change: insert a header above the range and explain why the relative position remains 5
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For find the last duplicate, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a household budget sheet. Transfer find the last duplicate into a household budget sheet. Preserve the governing rule—search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.—while identifying which cells, dimensions or error states change. Find the last duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a survey response table. Transfer find the last duplicate into a survey response table. Preserve the governing rule—search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.—while identifying which cells, dimensions or error states change. Find the last duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a data-cleaning checklist. Transfer find the last duplicate into a data-cleaning checklist. Preserve the governing rule—search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.—while identifying which cells, dimensions or error states change. Find the last duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a revision-topic list. Transfer find the last duplicate into a revision-topic list. Preserve the governing rule—search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.—while identifying which cells, dimensions or error states change. Find the last duplicate is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode -1 searches last-to-first while match_mode 0 still requires an exact match.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for find the last duplicate, predict the returned value or array, then insert a header above the range and explain why the relative position remains 5. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 5. The contrast follows from this rule: search_mode -1 searches last-to-first while match_mode 0 still requires an exact match. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for find the last duplicate, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain find the last duplicate, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Find the first duplicate · Full contents · Next: Use wildcard matching deliberately →

CHAPTER 5 OF 20 · Back to contents

5. Use wildcard matching deliberately

The short answer: Use wildcard matching deliberately is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the use wildcard matching deliberately example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A5 = {Math;Mathematics;Science;Mother Tongue}
=XMATCH("Math*",A2:A5,2)

Expected result and reasoning. The result is 1 because Math is the first first-to-last wildcard match. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Using an asterisk with exact mode 0 treats it as ordinary text rather than enabling the wildcard contract. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Was match_mode set to 2?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for use wildcard matching deliberately
• Variable to hold constant: the surrounding setup, so the comparison isolates use wildcard matching deliberately.
• Variable to change: search for “M?th” and compare
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For use wildcard matching deliberately, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a school-event roster. Transfer use wildcard matching deliberately into a school-event roster. Preserve the governing rule—match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.—while identifying which cells, dimensions or error states change. Use wildcard matching deliberately is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a subject-planning workbook. Transfer use wildcard matching deliberately into a subject-planning workbook. Preserve the governing rule—match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.—while identifying which cells, dimensions or error states change. Use wildcard matching deliberately is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a CCA attendance export. Transfer use wildcard matching deliberately into a CCA attendance export. Preserve the governing rule—match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.—while identifying which cells, dimensions or error states change. Use wildcard matching deliberately is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a library-book record. Transfer use wildcard matching deliberately into a library-book record. Preserve the governing rule—match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.—while identifying which cells, dimensions or error states change. Use wildcard matching deliberately is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for use wildcard matching deliberately, predict the returned value or array, then search for “M?th” and compare. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 1 because Math is the first first-to-last wildcard match. The contrast follows from this rule: match_mode 2 enables wildcard matching: asterisk matches a sequence and question mark matches one character. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for use wildcard matching deliberately, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain use wildcard matching deliberately, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Find the last duplicate · Full contents · Next: Escape a literal wildcard →

CHAPTER 6 OF 20 · Back to contents

6. Escape a literal wildcard

The short answer: Escape a literal wildcard is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: In wildcard mode, a tilde escapes the following wildcard character.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “In wildcard mode, a tilde escapes the following wildcard character.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the escape a literal wildcard example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A4 = {Plan*;Plan A;Plan B}
=XMATCH("Plan~*",A2:A4,2)

Expected result and reasoning. The result is 1 because the escaped asterisk is literal. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Writing Plan* without a tilde matches a wider set of text. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Is the asterisk data or a pattern operator?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for escape a literal wildcard
• Variable to hold constant: the surrounding setup, so the comparison isolates escape a literal wildcard.
• Variable to change: move Plan* to the third position
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For escape a literal wildcard, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a survey response table. Transfer escape a literal wildcard into a survey response table. Preserve the governing rule—In wildcard mode, a tilde escapes the following wildcard character.—while identifying which cells, dimensions or error states change. Escape a literal wildcard is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “In wildcard mode, a tilde escapes the following wildcard character.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a data-cleaning checklist. Transfer escape a literal wildcard into a data-cleaning checklist. Preserve the governing rule—In wildcard mode, a tilde escapes the following wildcard character.—while identifying which cells, dimensions or error states change. Escape a literal wildcard is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “In wildcard mode, a tilde escapes the following wildcard character.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a revision-topic list. Transfer escape a literal wildcard into a revision-topic list. Preserve the governing rule—In wildcard mode, a tilde escapes the following wildcard character.—while identifying which cells, dimensions or error states change. Escape a literal wildcard is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “In wildcard mode, a tilde escapes the following wildcard character.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a household budget sheet. Transfer escape a literal wildcard into a household budget sheet. Preserve the governing rule—In wildcard mode, a tilde escapes the following wildcard character.—while identifying which cells, dimensions or error states change. Escape a literal wildcard is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “In wildcard mode, a tilde escapes the following wildcard character.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for escape a literal wildcard, predict the returned value or array, then move Plan* to the third position. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 1 because the escaped asterisk is literal. The contrast follows from this rule: In wildcard mode, a tilde escapes the following wildcard character. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for escape a literal wildcard, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain escape a literal wildcard, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Use wildcard matching deliberately · Full contents · Next: Request the next smaller item →

CHAPTER 7 OF 20 · Back to contents

7. Request the next smaller item

The short answer: Request the next smaller item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: match_mode -1 returns an exact match or the next smaller item when an exact match is absent.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “match_mode -1 returns an exact match or the next smaller item when an exact match is absent.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the request the next smaller item example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A5 = {10;20;30;40}
=XMATCH(26,A2:A5,-1)

Expected result and reasoning. The result is 2, the position of 20. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Calling -1 a descending search confuses match_mode with search_mode. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which optional argument controls closeness and which controls direction?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for request the next smaller item
• Variable to hold constant: the surrounding setup, so the comparison isolates request the next smaller item.
• Variable to change: change 26 to 30 and observe the exact match at position 3
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For request the next smaller item, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a subject-planning workbook. Transfer request the next smaller item into a subject-planning workbook. Preserve the governing rule—match_mode -1 returns an exact match or the next smaller item when an exact match is absent.—while identifying which cells, dimensions or error states change. Request the next smaller item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode -1 returns an exact match or the next smaller item when an exact match is absent.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a CCA attendance export. Transfer request the next smaller item into a CCA attendance export. Preserve the governing rule—match_mode -1 returns an exact match or the next smaller item when an exact match is absent.—while identifying which cells, dimensions or error states change. Request the next smaller item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode -1 returns an exact match or the next smaller item when an exact match is absent.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a library-book record. Transfer request the next smaller item into a library-book record. Preserve the governing rule—match_mode -1 returns an exact match or the next smaller item when an exact match is absent.—while identifying which cells, dimensions or error states change. Request the next smaller item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode -1 returns an exact match or the next smaller item when an exact match is absent.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a school-event roster. Transfer request the next smaller item into a school-event roster. Preserve the governing rule—match_mode -1 returns an exact match or the next smaller item when an exact match is absent.—while identifying which cells, dimensions or error states change. Request the next smaller item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode -1 returns an exact match or the next smaller item when an exact match is absent.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for request the next smaller item, predict the returned value or array, then change 26 to 30 and observe the exact match at position 3. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 2, the position of 20. The contrast follows from this rule: match_mode -1 returns an exact match or the next smaller item when an exact match is absent. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for request the next smaller item, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain request the next smaller item, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Escape a literal wildcard · Full contents · Next: Request the next larger item →

CHAPTER 8 OF 20 · Back to contents

8. Request the next larger item

The short answer: Request the next larger item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: match_mode 1 returns an exact match or the next larger item when an exact match is absent.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “match_mode 1 returns an exact match or the next larger item when an exact match is absent.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the request the next larger item example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A5 = {10;20;30;40}
=XMATCH(26,A2:A5,1)

Expected result and reasoning. The result is 3, the position of 30. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Predicting position 2 reverses next larger and next smaller. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which candidate satisfies the requested boundary?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for request the next larger item
• Variable to hold constant: the surrounding setup, so the comparison isolates request the next larger item.
• Variable to change: change match_mode from 1 to -1
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For request the next larger item, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a data-cleaning checklist. Transfer request the next larger item into a data-cleaning checklist. Preserve the governing rule—match_mode 1 returns an exact match or the next larger item when an exact match is absent.—while identifying which cells, dimensions or error states change. Request the next larger item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode 1 returns an exact match or the next larger item when an exact match is absent.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a revision-topic list. Transfer request the next larger item into a revision-topic list. Preserve the governing rule—match_mode 1 returns an exact match or the next larger item when an exact match is absent.—while identifying which cells, dimensions or error states change. Request the next larger item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode 1 returns an exact match or the next larger item when an exact match is absent.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a household budget sheet. Transfer request the next larger item into a household budget sheet. Preserve the governing rule—match_mode 1 returns an exact match or the next larger item when an exact match is absent.—while identifying which cells, dimensions or error states change. Request the next larger item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode 1 returns an exact match or the next larger item when an exact match is absent.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a survey response table. Transfer request the next larger item into a survey response table. Preserve the governing rule—match_mode 1 returns an exact match or the next larger item when an exact match is absent.—while identifying which cells, dimensions or error states change. Request the next larger item is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “match_mode 1 returns an exact match or the next larger item when an exact match is absent.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for request the next larger item, predict the returned value or array, then change match_mode from 1 to -1. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 3, the position of 30. The contrast follows from this rule: match_mode 1 returns an exact match or the next larger item when an exact match is absent. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for request the next larger item, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain request the next larger item, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Request the next smaller item · Full contents · Next: Use ascending binary search only on sorted data →

CHAPTER 9 OF 20 · Back to contents

9. Use ascending binary search only on sorted data

The short answer: Use ascending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the use ascending binary search only on sorted data example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A6 = {10;20;30;40;50}
=XMATCH(40,A2:A6,0,2)

Expected result and reasoning. The result is 4 for this sorted fixture. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Treating binary mode as merely faster without protecting the sorted invariant is unsafe. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Can the learner prove the lookup array is sorted ascending?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for use ascending binary search only on sorted data
• Variable to hold constant: the surrounding setup, so the comparison isolates use ascending binary search only on sorted data.
• Variable to change: swap 20 and 50 and explain why the contract has been broken even if one lookup appears to work
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For use ascending binary search only on sorted data, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a CCA attendance export. Transfer use ascending binary search only on sorted data into a CCA attendance export. Preserve the governing rule—search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.—while identifying which cells, dimensions or error states change. Use ascending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a library-book record. Transfer use ascending binary search only on sorted data into a library-book record. Preserve the governing rule—search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.—while identifying which cells, dimensions or error states change. Use ascending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a school-event roster. Transfer use ascending binary search only on sorted data into a school-event roster. Preserve the governing rule—search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.—while identifying which cells, dimensions or error states change. Use ascending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a subject-planning workbook. Transfer use ascending binary search only on sorted data into a subject-planning workbook. Preserve the governing rule—search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.—while identifying which cells, dimensions or error states change. Use ascending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for use ascending binary search only on sorted data, predict the returned value or array, then swap 20 and 50 and explain why the contract has been broken even if one lookup appears to work. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 4 for this sorted fixture. The contrast follows from this rule: search_mode 2 performs a binary search and relies on lookup_array being sorted ascending; unsorted data can produce invalid results. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for use ascending binary search only on sorted data, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain use ascending binary search only on sorted data, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Request the next larger item · Full contents · Next: Use descending binary search only on sorted data →

CHAPTER 10 OF 20 · Back to contents

10. Use descending binary search only on sorted data

The short answer: Use descending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: search_mode -2 performs a binary search and relies on lookup_array being sorted descending.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “search_mode -2 performs a binary search and relies on lookup_array being sorted descending.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the use descending binary search only on sorted data example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A6 = {50;40;30;20;10}
=XMATCH(20,A2:A6,0,-2)

Expected result and reasoning. The result is 4. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Using -2 on ascending data violates the documented precondition. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Is the sort order descending across the entire lookup array?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for use descending binary search only on sorted data
• Variable to hold constant: the surrounding setup, so the comparison isolates use descending binary search only on sorted data.
• Variable to change: reverse the array and switch search_mode to 2
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For use descending binary search only on sorted data, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a revision-topic list. Transfer use descending binary search only on sorted data into a revision-topic list. Preserve the governing rule—search_mode -2 performs a binary search and relies on lookup_array being sorted descending.—while identifying which cells, dimensions or error states change. Use descending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode -2 performs a binary search and relies on lookup_array being sorted descending.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a household budget sheet. Transfer use descending binary search only on sorted data into a household budget sheet. Preserve the governing rule—search_mode -2 performs a binary search and relies on lookup_array being sorted descending.—while identifying which cells, dimensions or error states change. Use descending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode -2 performs a binary search and relies on lookup_array being sorted descending.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a survey response table. Transfer use descending binary search only on sorted data into a survey response table. Preserve the governing rule—search_mode -2 performs a binary search and relies on lookup_array being sorted descending.—while identifying which cells, dimensions or error states change. Use descending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode -2 performs a binary search and relies on lookup_array being sorted descending.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a data-cleaning checklist. Transfer use descending binary search only on sorted data into a data-cleaning checklist. Preserve the governing rule—search_mode -2 performs a binary search and relies on lookup_array being sorted descending.—while identifying which cells, dimensions or error states change. Use descending binary search only on sorted data is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “search_mode -2 performs a binary search and relies on lookup_array being sorted descending.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for use descending binary search only on sorted data, predict the returned value or array, then reverse the array and switch search_mode to 2. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 4. The contrast follows from this rule: search_mode -2 performs a binary search and relies on lookup_array being sorted descending. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for use descending binary search only on sorted data, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain use descending binary search only on sorted data, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Use ascending binary search only on sorted data · Full contents · Next: Search a horizontal array →

CHAPTER 11 OF 20 · Back to contents

11. Search a horizontal array

The short answer: Search a horizontal array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the search a horizontal array example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

B2:E2 = {Jan,Feb,Mar,Apr}
=XMATCH("Mar",B2:E2)

Expected result and reasoning. The result is 3. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Returning worksheet column D mistakes absolute coordinates for relative position. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Where does the lookup array begin, and what is Mar’s position inside it?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for search a horizontal array
• Variable to hold constant: the surrounding setup, so the comparison isolates search a horizontal array.
• Variable to change: start the range at C2 instead and explain why the result remains 3
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For search a horizontal array, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a library-book record. Transfer search a horizontal array into a library-book record. Preserve the governing rule—lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.—while identifying which cells, dimensions or error states change. Search a horizontal array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a school-event roster. Transfer search a horizontal array into a school-event roster. Preserve the governing rule—lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.—while identifying which cells, dimensions or error states change. Search a horizontal array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a subject-planning workbook. Transfer search a horizontal array into a subject-planning workbook. Preserve the governing rule—lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.—while identifying which cells, dimensions or error states change. Search a horizontal array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a CCA attendance export. Transfer search a horizontal array into a CCA attendance export. Preserve the governing rule—lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.—while identifying which cells, dimensions or error states change. Search a horizontal array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “lookup_array may be a row or a column; the returned position is relative within that one-dimensional array.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for search a horizontal array, predict the returned value or array, then start the range at C2 instead and explain why the result remains 3. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 3. The contrast follows from this rule: lookup_array may be a row or a column; the returned position is relative within that one-dimensional array. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for search a horizontal array, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain search a horizontal array, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Use descending binary search only on sorted data · Full contents · Next: Treat text case as equivalent →

CHAPTER 12 OF 20 · Back to contents

12. Treat text case as equivalent

The short answer: Treat text case as equivalent is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: XMATCH text matching is not case-sensitive.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “XMATCH text matching is not case-sensitive.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the treat text case as equivalent example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A4 = {math;SCIENCE;English}
=XMATCH("Science",A2:A4)

Expected result and reasoning. The result is 2. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Expecting #N/A because of letter case assumes a case-sensitive comparison. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Does the documented lookup distinguish uppercase from lowercase here?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for treat text case as equivalent
• Variable to hold constant: the surrounding setup, so the comparison isolates treat text case as equivalent.
• Variable to change: add both Science and SCIENCE, then compare first and reverse search
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For treat text case as equivalent, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a household budget sheet. Transfer treat text case as equivalent into a household budget sheet. Preserve the governing rule—XMATCH text matching is not case-sensitive.—while identifying which cells, dimensions or error states change. Treat text case as equivalent is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH text matching is not case-sensitive.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a survey response table. Transfer treat text case as equivalent into a survey response table. Preserve the governing rule—XMATCH text matching is not case-sensitive.—while identifying which cells, dimensions or error states change. Treat text case as equivalent is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH text matching is not case-sensitive.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a data-cleaning checklist. Transfer treat text case as equivalent into a data-cleaning checklist. Preserve the governing rule—XMATCH text matching is not case-sensitive.—while identifying which cells, dimensions or error states change. Treat text case as equivalent is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH text matching is not case-sensitive.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a revision-topic list. Transfer treat text case as equivalent into a revision-topic list. Preserve the governing rule—XMATCH text matching is not case-sensitive.—while identifying which cells, dimensions or error states change. Treat text case as equivalent is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH text matching is not case-sensitive.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for treat text case as equivalent, predict the returned value or array, then add both Science and SCIENCE, then compare first and reverse search. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 2. The contrast follows from this rule: XMATCH text matching is not case-sensitive. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for treat text case as equivalent, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain treat text case as equivalent, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Search a horizontal array · Full contents · Next: Diagnose number stored as text →

CHAPTER 13 OF 20 · Back to contents

13. Diagnose number stored as text

The short answer: Diagnose number stored as text is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: An exact numeric lookup does not reliably match a text representation merely because both display the same digits.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “An exact numeric lookup does not reliably match a text representation merely because both display the same digits.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the diagnose number stored as text example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A4 contains text "10", number 20, number 30
=XMATCH(10,A2:A4,0)

Expected result and reasoning. The intended diagnostic result is #N/A for the text “10” versus numeric 10 fixture. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Visual sameness in the cell grid does not guarantee the same value type. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “What do ISTEXT and ISNUMBER report for the candidate cell?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for diagnose number stored as text
• Variable to hold constant: the surrounding setup, so the comparison isolates diagnose number stored as text.
• Variable to change: convert A2 with VALUE and retest
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For diagnose number stored as text, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a school-event roster. Transfer diagnose number stored as text into a school-event roster. Preserve the governing rule—An exact numeric lookup does not reliably match a text representation merely because both display the same digits.—while identifying which cells, dimensions or error states change. Diagnose number stored as text is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “An exact numeric lookup does not reliably match a text representation merely because both display the same digits.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a subject-planning workbook. Transfer diagnose number stored as text into a subject-planning workbook. Preserve the governing rule—An exact numeric lookup does not reliably match a text representation merely because both display the same digits.—while identifying which cells, dimensions or error states change. Diagnose number stored as text is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “An exact numeric lookup does not reliably match a text representation merely because both display the same digits.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a CCA attendance export. Transfer diagnose number stored as text into a CCA attendance export. Preserve the governing rule—An exact numeric lookup does not reliably match a text representation merely because both display the same digits.—while identifying which cells, dimensions or error states change. Diagnose number stored as text is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “An exact numeric lookup does not reliably match a text representation merely because both display the same digits.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a library-book record. Transfer diagnose number stored as text into a library-book record. Preserve the governing rule—An exact numeric lookup does not reliably match a text representation merely because both display the same digits.—while identifying which cells, dimensions or error states change. Diagnose number stored as text is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “An exact numeric lookup does not reliably match a text representation merely because both display the same digits.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for diagnose number stored as text, predict the returned value or array, then convert A2 with VALUE and retest. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The intended diagnostic result is #N/A for the text “10” versus numeric 10 fixture. The contrast follows from this rule: An exact numeric lookup does not reliably match a text representation merely because both display the same digits. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for diagnose number stored as text, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain diagnose number stored as text, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Treat text case as equivalent · Full contents · Next: Handle not found with IFNA →

CHAPTER 14 OF 20 · Back to contents

14. Handle not found with IFNA

The short answer: Handle not found with IFNA is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the handle not found with ifna example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A4 = {Red;Blue;Green}
=IFNA(XMATCH("Gold",A2:A4),"Not listed")

Expected result and reasoning. The result is Not listed. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Wrapping every formula in IFERROR can hide a malformed reference or other fault. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Is the anticipated failure specifically a not-found #N/A?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for handle not found with ifna
• Variable to hold constant: the surrounding setup, so the comparison isolates handle not found with ifna.
• Variable to change: change IFNA to IFERROR and discuss the broader catch
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For handle not found with ifna, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a survey response table. Transfer handle not found with ifna into a survey response table. Preserve the governing rule—IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.—while identifying which cells, dimensions or error states change. Handle not found with IFNA is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a data-cleaning checklist. Transfer handle not found with ifna into a data-cleaning checklist. Preserve the governing rule—IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.—while identifying which cells, dimensions or error states change. Handle not found with IFNA is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a revision-topic list. Transfer handle not found with ifna into a revision-topic list. Preserve the governing rule—IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.—while identifying which cells, dimensions or error states change. Handle not found with IFNA is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a household budget sheet. Transfer handle not found with ifna into a household budget sheet. Preserve the governing rule—IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.—while identifying which cells, dimensions or error states change. Handle not found with IFNA is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for handle not found with ifna, predict the returned value or array, then change IFNA to IFERROR and discuss the broader catch. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is Not listed. The contrast follows from this rule: IFNA can replace #N/A from an unsuccessful XMATCH without hiding unrelated error types. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for handle not found with ifna, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain handle not found with ifna, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Diagnose number stored as text · Full contents · Next: Return a value with INDEX and XMATCH →

CHAPTER 15 OF 20 · Back to contents

15. Return a value with INDEX and XMATCH

The short answer: Return a value with INDEX and XMATCH is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the return a value with index and xmatch example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A4 = {Red;Blue;Green}
B2:B4 = {3;7;5}
=INDEX(B2:B4,XMATCH("Blue",A2:A4))

Expected result and reasoning. The result is 7. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Using different-sized or misaligned ranges breaks the row correspondence. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Do the lookup and return ranges describe the same records in the same order?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for return a value with index and xmatch
• Variable to hold constant: the surrounding setup, so the comparison isolates return a value with index and xmatch.
• Variable to change: look up Green and predict 5
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For return a value with index and xmatch, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a subject-planning workbook. Transfer return a value with index and xmatch into a subject-planning workbook. Preserve the governing rule—INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.—while identifying which cells, dimensions or error states change. Return a value with INDEX and XMATCH is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a CCA attendance export. Transfer return a value with index and xmatch into a CCA attendance export. Preserve the governing rule—INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.—while identifying which cells, dimensions or error states change. Return a value with INDEX and XMATCH is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a library-book record. Transfer return a value with index and xmatch into a library-book record. Preserve the governing rule—INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.—while identifying which cells, dimensions or error states change. Return a value with INDEX and XMATCH is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a school-event roster. Transfer return a value with index and xmatch into a school-event roster. Preserve the governing rule—INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.—while identifying which cells, dimensions or error states change. Return a value with INDEX and XMATCH is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for return a value with index and xmatch, predict the returned value or array, then look up Green and predict 5. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 7. The contrast follows from this rule: INDEX can use XMATCH’s relative position to return a corresponding value from another aligned range. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for return a value with index and xmatch, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain return a value with index and xmatch, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Handle not found with IFNA · Full contents · Next: Perform a two-way lookup →

CHAPTER 16 OF 20 · Back to contents

16. Perform a two-way lookup

The short answer: Perform a two-way lookup is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the perform a two-way lookup example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A4 = {Ali;Bea;Chen}
B1:D1 = {Jan,Feb,Mar}
B2:D4 = {4,5,6;7,8,9;10,11,12}
=INDEX(B2:D4,XMATCH("Bea",A2:A4),XMATCH("Mar",B1:D1))

Expected result and reasoning. The result is 9. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Swapping the two positions sends a month position to the row argument and a name position to the column argument. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Which XMATCH describes rows and which describes columns?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for perform a two-way lookup
• Variable to hold constant: the surrounding setup, so the comparison isolates perform a two-way lookup.
• Variable to change: look up Chen in Feb
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For perform a two-way lookup, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a data-cleaning checklist. Transfer perform a two-way lookup into a data-cleaning checklist. Preserve the governing rule—Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.—while identifying which cells, dimensions or error states change. Perform a two-way lookup is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a revision-topic list. Transfer perform a two-way lookup into a revision-topic list. Preserve the governing rule—Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.—while identifying which cells, dimensions or error states change. Perform a two-way lookup is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a household budget sheet. Transfer perform a two-way lookup into a household budget sheet. Preserve the governing rule—Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.—while identifying which cells, dimensions or error states change. Perform a two-way lookup is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a survey response table. Transfer perform a two-way lookup into a survey response table. Preserve the governing rule—Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.—while identifying which cells, dimensions or error states change. Perform a two-way lookup is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for perform a two-way lookup, predict the returned value or array, then look up Chen in Feb. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is 9. The contrast follows from this rule: Two XMATCH calls can supply row_num and column_num to INDEX for a two-dimensional lookup. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for perform a two-way lookup, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain perform a two-way lookup, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Return a value with INDEX and XMATCH · Full contents · Next: Match multiple conditions with a Boolean array →

CHAPTER 17 OF 20 · Back to contents

17. Match multiple conditions with a Boolean array

The short answer: Match multiple conditions with a Boolean array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the match multiple conditions with a boolean array example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A5={Math;Math;Science;Science}
B2:B5={Mon;Tue;Mon;Tue}
=XMATCH(1,(A2:A5="Science")*(B2:B5="Tue"),0)

Expected result and reasoning. The combined Boolean array is {0;0;0;1}, so XMATCH returns position 4. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Adding Boolean arrays would also produce 1 where exactly one condition is true. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “What numeric pattern does the combined Boolean array produce?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for match multiple conditions with a boolean array
• Variable to hold constant: the surrounding setup, so the comparison isolates match multiple conditions with a boolean array.
• Variable to change: change Tue to Mon and trace the four array entries
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For match multiple conditions with a boolean array, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a CCA attendance export. Transfer match multiple conditions with a boolean array into a CCA attendance export. Preserve the governing rule—A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.—while identifying which cells, dimensions or error states change. Match multiple conditions with a Boolean array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a library-book record. Transfer match multiple conditions with a boolean array into a library-book record. Preserve the governing rule—A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.—while identifying which cells, dimensions or error states change. Match multiple conditions with a Boolean array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a school-event roster. Transfer match multiple conditions with a boolean array into a school-event roster. Preserve the governing rule—A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.—while identifying which cells, dimensions or error states change. Match multiple conditions with a Boolean array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a subject-planning workbook. Transfer match multiple conditions with a boolean array into a subject-planning workbook. Preserve the governing rule—A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.—while identifying which cells, dimensions or error states change. Match multiple conditions with a Boolean array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for match multiple conditions with a boolean array, predict the returned value or array, then change Tue to Mon and trace the four array entries. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The combined Boolean array is {0;0;0;1}, so XMATCH returns position 4. The contrast follows from this rule: A Boolean multiplication array contains 1 only where all stated conditions are TRUE; XMATCH can find that 1. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for match multiple conditions with a boolean array, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain match multiple conditions with a boolean array, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Perform a two-way lookup · Full contents · Next: Use a spilled lookup array →

CHAPTER 18 OF 20 · Back to contents

18. Use a spilled lookup array

The short answer: Use a spilled lookup array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the use a spilled lookup array example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

D2# spills {Red;Blue;Green}
=XMATCH("Blue",D2#)

Expected result and reasoning. Blue is the second member of the current D2# spill, so XMATCH returns 2. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Hard-coding D2:D4 loses automatic resizing when the upstream spill changes. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Does D2 currently anchor a spill, and what are its dimensions?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for use a spilled lookup array
• Variable to hold constant: the surrounding setup, so the comparison isolates use a spilled lookup array.
• Variable to change: add Gold to the upstream source
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For use a spilled lookup array, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a revision-topic list. Transfer use a spilled lookup array into a revision-topic list. Preserve the governing rule—The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.—while identifying which cells, dimensions or error states change. Use a spilled lookup array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a household budget sheet. Transfer use a spilled lookup array into a household budget sheet. Preserve the governing rule—The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.—while identifying which cells, dimensions or error states change. Use a spilled lookup array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a survey response table. Transfer use a spilled lookup array into a survey response table. Preserve the governing rule—The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.—while identifying which cells, dimensions or error states change. Use a spilled lookup array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a data-cleaning checklist. Transfer use a spilled lookup array into a data-cleaning checklist. Preserve the governing rule—The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.—while identifying which cells, dimensions or error states change. Use a spilled lookup array is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “The spill operator # refers to the full current dynamic-array result beginning at the anchor cell.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for use a spilled lookup array, predict the returned value or array, then add Gold to the upstream source. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. Blue is the second member of the current D2# spill, so XMATCH returns 2. The contrast follows from this rule: The spill operator # refers to the full current dynamic-array result beginning at the anchor cell. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for use a spilled lookup array, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain use a spilled lookup array, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Match multiple conditions with a Boolean array · Full contents · Next: Check version compatibility →

CHAPTER 19 OF 20 · Back to contents

19. Check version compatibility

The short answer: Check version compatibility is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the check version compatibility example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

Open a new workbook and enter =XMATCH("b",{"a","b","c"})

Expected result and reasoning. A supported installation returns 2; an unsupported installation may show _xlfn.XMATCH or #NAME?. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Calling #NAME? a logic error before checking version wastes diagnostic effort. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “Does the installed Excel version include XMATCH?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for check version compatibility
• Variable to hold constant: the surrounding setup, so the comparison isolates check version compatibility.
• Variable to change: open the same workbook in a documented supported version
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For check version compatibility, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a library-book record. Transfer check version compatibility into a library-book record. Preserve the governing rule—XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.—while identifying which cells, dimensions or error states change. Check version compatibility is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a school-event roster. Transfer check version compatibility into a school-event roster. Preserve the governing rule—XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.—while identifying which cells, dimensions or error states change. Check version compatibility is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a subject-planning workbook. Transfer check version compatibility into a subject-planning workbook. Preserve the governing rule—XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.—while identifying which cells, dimensions or error states change. Check version compatibility is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a CCA attendance export. Transfer check version compatibility into a CCA attendance export. Preserve the governing rule—XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.—while identifying which cells, dimensions or error states change. Check version compatibility is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for check version compatibility, predict the returned value or array, then open the same workbook in a documented supported version. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. A supported installation returns 2; an unsupported installation may show _xlfn.XMATCH or #NAME?. The contrast follows from this rule: XMATCH is documented for Microsoft 365, Excel 2024 and Excel 2021 families, not every older perpetual release. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for check version compatibility, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain check version compatibility, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Use a spilled lookup array · Full contents · Next: Choose XMATCH for positions →

CHAPTER 20 OF 20 · Back to contents

20. Choose XMATCH for positions

The short answer: Choose XMATCH for positions is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. The governing rule is: Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.. This is the sentence a learner should be able to say before touching a keyboard. It turns a vague topic into a testable model and gives a parent or tutor something precise to listen for.

Excel applies the rule “Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.”. A small grid makes the input, returned shape and spill behaviour visible while excluding unrelated workbook state. That mechanism matters because superficially similar examples can produce different results. A reliable explanation names the relevant object, identifies the operation performed on it, and states when the decision is made. “It works” is not enough; mastery means predicting the result and explaining why a nearby example behaves differently.

Worked example: predict before running

Read the choose xmatch for positions example once without executing it. Mark the exact input, the operation under study, and the expected observable result. Then write a one-sentence prediction. Only after that should the learner run the example. This order separates understanding from trial-and-error and makes any surprise diagnostically useful.

A2:A4={Red;Blue;Green}
=XMATCH("Blue",A2:A4)

Expected result and reasoning. The result is position 2, which is exactly the requested output. The important evidence is not merely the printed value or exit status. It is the connection between that evidence and the rule above. Say the chain aloud: input → relevant rule → operation → observable result. If a learner cannot supply the middle two links, the right response is to revisit the model rather than add more examples.

Near miss. Forcing a position function to return entire records makes the formula harder to explain. This is tempting because it uses a familiar word or visual resemblance. Correct it by changing only one variable at a time and comparing the two cases side by side. A good contrast preserves everything except the feature named in the rule.

Diagnostic route when the answer is wrong

Use a four-pass diagnosis. Pass one: classify. Identify the kind of value or state being examined. Pass two: trace. Follow the operation in the order defined by the platform or specification. Pass three: observe. Capture a minimal output, not a screenshot of a large project. Pass four: contrast. Change one feature and run again. For this chapter, the highest-value question is: “What shape and kind of result does the task actually require?”

• Evidence to collect: the exact source cells, formula, Excel version, spill area, observed result and governing rule for choose xmatch for positions
• Variable to hold constant: the surrounding setup, so the comparison isolates choose xmatch for positions.
• Variable to change: rewrite a value-returning task with XLOOKUP instead
• Explanation standard: name the rule, not merely the successful command or copied syntax.

For choose xmatch for positions, a common unproductive move is to make several changes at once. That can make the symptom disappear while leaving the misconception intact. Reset to the smallest example, keep a brief prediction log, and preserve both the successful and unsuccessful case. The pair becomes a reusable test fixture and a much clearer revision note than a paragraph of definitions.

Four transfer cases

Transfer case 1: a household budget sheet. Transfer choose xmatch for positions into a household budget sheet. Preserve the governing rule—Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.—while identifying which cells, dimensions or error states change. Choose XMATCH for positions is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 2: a survey response table. Transfer choose xmatch for positions into a survey response table. Preserve the governing rule—Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.—while identifying which cells, dimensions or error states change. Choose XMATCH for positions is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 3: a data-cleaning checklist. Transfer choose xmatch for positions into a data-cleaning checklist. Preserve the governing rule—Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.—while identifying which cells, dimensions or error states change. Choose XMATCH for positions is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Transfer case 4: a revision-topic list. Transfer choose xmatch for positions into a revision-topic list. Preserve the governing rule—Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.—while identifying which cells, dimensions or error states change. Choose XMATCH for positions is best learned as a decision about Excel’s documented array contract rather than as a formula fragment to memorise. Record source grid, prediction, returned shape, observation and Excel version so disagreement becomes diagnosable. Start by naming the observable evidence, then apply the chapter rule “Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows.”. Do not jump straight to a command or syntax change. First predict the outcome, perform the smallest safe check, and compare the result with that prediction. This makes the case useful beyond the immediate exercise: the learner is practising a decision process, not memorising a one-off answer. If the result differs, record what changed—platform, input shape, repository state, document tree, or evaluation order—before revising the model.

Practice, pause point, and explained answer

Task. Rebuild the example for choose xmatch for positions, predict the returned value or array, then rewrite a value-returning task with XLOOKUP instead. Explain both outcomes without trial and error. Before checking anything, write (1) the predicted result, (2) the rule that justifies it, and (3) one nearby variation that should change the result. A complete response should be understandable to someone who has not seen the example.

Explained answer. The result is position 2, which is exactly the requested output. The contrast follows from this rule: Choose XMATCH when the job is to return a relative position; prefer XLOOKUP for a corresponding value and FILTER for all matching rows. The reasoning earns more attention than the final value. If the learner reached the correct result by guessing, repeat the task with the variation they proposed. If they can predict both cases and explain the contrast, the idea is beginning to transfer.

Parent and tutor decision point

Ask the learner to sketch the returned shape and name the evidence that matters for choose xmatch for positions, then request one controlled variation. A correct grid without a causal explanation is not yet mastery. A useful conversation is brief: ask for the rule, ask for a prediction, then ask which observation would disprove it. Avoid turning the exchange into a vocabulary quiz. The goal is to hear a causal explanation and see a controlled test. Praise a corrected prediction when the learner uses evidence well; that is stronger progress than protecting an unexamined first answer.

Ready to move on? Move forward when the learner can explain choose xmatch for positions, solve the worked case without prompts, and handle at least two transfer cases with a changed context. Revisit this chapter when the explanation relies on “usually”, “looks like”, or “the computer says so” without identifying the governing rule.

← Previous: Check version compatibility · Full contents · Parent guide →

Parent guide: what mastery looks like

A learner has not mastered Excel XMATCH merely because one polished example runs. Look for four behaviours: they can state the rule in plain language; predict before executing; isolate one changed condition; and explain why a transfer case is the same problem or a different one. Those behaviours make progress visible without requiring a parent to become a specialist.

Use a ten-minute check-in. Ask the learner to choose one chapter, reconstruct its example from memory, and narrate the causal chain. Then change one input. If the learner can update the prediction and justify the change, stop. More repetitions are not automatically better. Save the two-case pair and return to it after a few days.

If every answer is hesitant, reduce the scope. Return to the earliest chapter whose rule cannot be stated accurately. If explanations are fluent but real tasks still fail, move to a capstone because the missing skill is likely transfer or diagnosis. If frustration rises, separate typing errors from conceptual errors and solve only one category in that session.

Capstone practice with explained routes

Capstone 1: Rebuild the smallest trustworthy fixture

Scenario. Create a disposable workbook fixture that demonstrates position-based lookup reasoning without unrelated sheets or formatting.

Route. Record version, source cells, formula, predicted shape and output. Keep one nearby contrast. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. Another learner can reproduce the observation and explain the separating rule. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 2: Diagnose a believable wrong explanation

Scenario. A learner has the correct output but the wrong mechanism.

Route. Change the condition the wrong explanation says matters while holding the real condition constant. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. The controlled contrast exposes the misconception. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 3: Handle a version boundary

Scenario. Two installations produce different observations.

Route. Capture Excel versions, platform, exact formula and workbook state. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. Bind the claim to the documented availability and retest. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 4: Design a parent check-in

Scenario. A parent has ten minutes and no spreadsheet background.

Route. Ask for one rule, prediction, sketch, calculation and changed condition. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. The learner demonstrates mastery through causal explanation. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 5: Transfer into a larger workbook

Scenario. The isolated fixture works but a real workbook does not.

Route. Preserve the fixture, then compare tables, data types, spill cells and references one at a time. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. The first dependency that changes the result becomes the next diagnostic focus. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 6: Write a regression check

Scenario. The workbook will receive new rows next week.

Route. Save the positive case, near miss and expected dimensions. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. A useful check reports a meaningful changed observation. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 7: Explain a limitation honestly

Scenario. The function does not cover an important data-cleaning job or is unavailable.

Route. Name the limitation and the safer route. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. Good advice is bounded rather than stretched into a guarantee. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Capstone 8: Choose the right function

Scenario. Several dynamic-array functions appear interchangeable.

Route. Define the desired values, shape, ordering, compatibility and error behaviour. Begin with a written prediction, collect the smallest sufficient evidence, and explain which observation would make you revise the conclusion.

Explained resolution. Choose the contract that matches the job and reject a tempting mismatch. A strong submission includes the result, the causal chain and a controlled contrasting case. It does not hide uncertainty: if platform, version or environment matters, state that boundary.

Frequently asked questions

Is Excel XMATCH mainly a formula to memorise?

No. The formula is the handle. Mastery means stating the array contract, predicting the returned shape, checking boundary cases and choosing another function when the contract does not fit.

How long should one practice session be?

Ten to twenty focused minutes is enough for one rule, one worked example, one near miss and one explanation. Save the fixture and retrieve it again after a gap.

Should every option be memorised?

No. Memorise the default behaviour, then organise optional arguments by the decision they control: matching, search direction, sorting, ignoring, scan order or padding.

What can a non-specialist parent ask?

Ask for a prediction, the returned shape, the rule behind it, the observation that could disprove it and one changed case.

When is copying an example useful?

Copying establishes a baseline. It becomes learning after the student predicts, reconstructs, varies and explains it.

How do we separate setup errors from concept errors?

Run a known-good tiny workbook fixture first. If it fails, inspect Excel version, transcription and spill space. If it passes but a variation is predicted wrongly, the misconception is conceptual.

Should practice start inside an important workbook?

No. Begin in a new disposable workbook or a copy. Transfer the verified rule only after the learner can explain the result.

What belongs in revision notes?

Keep one rule, the smallest successful formula, a near miss, observed output, Excel version and a diagnostic question.

What if two computers disagree?

Treat the difference as evidence. Record Excel versions, source data, exact formula and spill area, then consult Microsoft Support.

When should the learner move on?

Move on when the learner can explain the rule, predict two contrasts, diagnose a broken case and transfer the idea without step-by-step prompting.

References and verification notes

Primary technical source: Official Microsoft Support documentation for XMATCH. Microsoft documents the relative-position result, syntax, exact default, approximate and wildcard match modes, forward, reverse and sorted binary search modes, INDEX combinations and supported Excel editions.

The worked examples were checked with independent array walks and a small JavaScript reference model for exact, reverse, approximate, wildcard and binary examples, with version and error boundaries checked against Microsoft Support. The checks confirm the specific examples in this guide; they do not promise identical behaviour on an older version, a different operating system, a different browser implementation, or a repository with different state. Keep the version and environment with the saved fixture.

Last reviewed: 27 October 2026. This article is educational guidance for families and learners. It does not advertise a physical Punggol outlet or make admissions, affiliation, score or outcome guarantees.