The key is reported as the position
MATCH returns an array position. Use the returned position to inspect its source row.
Compare MATCH modes and maintained groups
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill K2:Q7 for the numeric queries J2:J7. K returns the first exact position inside unsorted A2:A7, or No exact. L returns the position of the largest key at or below the query in ascending D2:D7, or No lower. M returns the position of the smallest key at or above the query in descending G2:G7, or No upper. N and O return the maintained text group from E and H at the corresponding approximate position; P returns the exact group from B. Q independently returns the nearest-lower group with VLOOKUP on the ascending D2:E7 policy and must agree with N. Positions are 1-based within the selected six-cell range, not spreadsheet row numbers or matched key values. Use explicit MATCH modes 0,1,-1, INDEX for N:P groups, and explicit approximate VLOOKUP for Q. Exact keys may be unsorted or duplicated and select the first occurrence. Lower keys must be strictly increasing and upper keys strictly decreasing; these two policies are editable independently. Keys and queries are finite stored numbers within ±1,000,000. Group labels are nonempty ASCII text up to 40 characters, including a possible text 0 label. Copy within each output column only.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: K2, L2, M2, N2, O2, P2, Q2, K3, L3, M3, N3, O3, P3, Q3, K4, L4, M4, N4, O4, P4, Q4, K5, L5, M5, N5, O5, P5, Q5, K6, L6, M6, N6, O6, P6, Q6, K7, L7, M7, N7, O7, P7, Q7. Browser calculations may differ from your Excel version.
Inspect current stored results and formulas
| Cell | Current value | Applied formula |
|---|---|---|
| K2 | — | No formula |
| L2 | — | No formula |
| M2 | — | No formula |
| N2 | — | No formula |
| O2 | — | No formula |
| P2 | — | No formula |
| Q2 | — | No formula |
| K3 | — | No formula |
| L3 | — | No formula |
| M3 | — | No formula |
| N3 | — | No formula |
| O3 | — | No formula |
| P3 | — | No formula |
| Q3 | — | No formula |
| K4 | — | No formula |
| L4 | — | No formula |
| M4 | — | No formula |
| N4 | — | No formula |
| O4 | — | No formula |
| P4 | — | No formula |
| Q4 | — | No formula |
| K5 | — | No formula |
| L5 | — | No formula |
| M5 | — | No formula |
| N5 | — | No formula |
| O5 | — | No formula |
| P5 | — | No formula |
| Q5 | — | No formula |
| K6 | — | No formula |
| L6 | — | No formula |
| M6 | — | No formula |
| N6 | — | No formula |
| O6 | — | No formula |
| P6 | — | No formula |
| Q6 | — | No formula |
| K7 | — | No formula |
| L7 | — | No formula |
| M7 | — | No formula |
| N7 | — | No formula |
| O7 | — | No formula |
| P7 | — | No formula |
| Q7 | — | No formula |
Change an actual source value
Exact source A may be unsorted or duplicated. Keep lower keys D strictly ascending and upper keys G strictly descending. Group labels are editable stored text, including a valid text 0 category; queries and keys stay numeric.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Inspect exact, nearest-lower and nearest-upper positions from differently ordered sources, then read the selected category from its actual row.
THE QUICK ANSWER01
Inspect exact, nearest-lower and nearest-upper positions from differently ordered sources, then read the selected category from its actual row.
ƒx
=IFNA(MATCH(J2,$A$2:$A$7,0),"No exact")For the example data
K2 No exactL2 No lowerM2 6N2 No lowerO2 Upper-1P2 No exactQ2 No lowerK3 2L3 1M3 6N3 Lower-1O3 Upper-1P3 Exact-1Q3 Lower-1K4 No exactL4 2M4 4N4 Lower-2O4 Upper-3P4 No exactQ4 Lower-2K5 1L5 3M5 4N5 Lower-3O5 Upper-3P5 Exact-3Q5 Lower-3K6 3L6 6M6 1N6 Lower-6O6 Upper-6P6 Exact-6Q6 Lower-6K7 No exactL7 6M7 No upperN7 Lower-6O7 No upperP7 No exactQ7 Lower-6
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Positions and selected keys are different outputs
In the demo exact source, key 25 occupies position 1. In the ascending lower source it occupies position 3; in the descending upper source it occupies position 4. All refer to source-array positions, not the row number of the request.
Approximate direction and source order belong together
Nearest lower uses mode 1 on ascending keys. Nearest upper uses mode -1 on descending keys. Exact matching does not need sorting. This exercise validates source order before grading; it does not claim that Excel always reports an error for an incorrectly ordered array.
A group is maintained data
The lower and upper tables are separate policies. Editing an E or H group updates every query selecting that row; no nested branch constants need to be rewritten. The source label can be text 0 and still be a valid found category.
The missing direction matters
No exact, No lower and No upper represent different questions. A query can lack an exact match while still belonging to both a nearest-lower and nearest-upper group.
Read the full step-by-step walkthrough
- Read three source-order contracts
Exact matching searches A without requiring order. The lower-key source D ascends, while the upper-key source G descends. The maintained group labels belong to each actual source row.
- Return the first exact relative position
MATCH with mode 0 returns a 1-based position inside A2:A7. It does not return the matching key or the spreadsheet row number. Reordering exact-source rows changes positions while a matching group can remain the same.
- Find the nearest lower key on an ascending source
Mode 1 selects the largest available key less than or equal to the query. A query below every minimum has no qualifying lower record. Keep the source strictly ascending in this exercise.
- Find the nearest upper key on a descending source
Mode -1 selects the smallest available key greater than or equal to the query. Its required source order is descending. A query above every maximum has no qualifying upper record.
- Read the maintained group from the selected row
INDEX consumes the relative MATCH position inside the corresponding group column. Changing a selected group label must update the report without rewriting lookup logic. A category named 0 stays stored text.
- Compare approximate VLOOKUP with the same lower policy
Q uses VLOOKUP with TRUE on the ascending D2:E7 source. It selects the same lower group as INDEX/MATCH in N. This differs from the FALSE wildcard request task: the sorted numeric source is part of this approximate lookup contract.
- Test endpoints, gaps and source changes
Fill each of K through Q separately. Compare exact thresholds, intermediate queries and both outside endpoints. An invalid approximate-source order is refused by the exercise rather than advertised as a reliable lookup result.
Things to watch
The mode argument is omitted
The default is approximate ascending behavior, which is not exact matching.
Ascending and descending requirements are swapped
Match type and source order must agree; an arbitrary returned number is not evidence that the search is valid.
The group label is hardcoded
Read the category from its actual source row so policy maintenance can change it.
About this example
Original bounded lookup interpretation/position tasks; browser/current-file checks are separate from native-client recalculation, rendering and edit/save certification.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.