Every request is escaped
A Pattern request such as A* is intentional syntax and must not become a literal identifier.
Choose wildcard searches or literal product codes
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill H2:J7 from the eight-row A2:C9 source. Each request has lookup text in F and an interpretation in G: Pattern uses legacy VLOOKUP wildcard syntax; Literal searches the complete stored identifier, including *, ? and ~. Matching ignores ASCII letter case, keeps other characters and leading zeroes, and selects the first physical matching row. H returns Price, I returns Units and J the actual matched product code. A missing request returns No match in all three cells. Use VLOOKUP with an explicit fourth argument for exact/wildcard lookup; approximate lookup is a separate sorted-source task. All identifiers and requests are nonempty ASCII text up to 40 characters; prices/units are finite stored numbers within ±1,000,000. Copy within each result column, keeping the source fixed.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: H2, I2, J2, H3, I3, J3, H4, I4, J4, H5, I5, J5, H6, I6, J6, H7, I7, J7. Browser calculations may differ from your Excel version.
Inspect current stored results and formulas
| Cell | Current value | Applied formula |
|---|---|---|
| H2 | — | No formula |
| I2 | — | No formula |
| J2 | — | No formula |
| H3 | — | No formula |
| I3 | — | No formula |
| J3 | — | No formula |
| H4 | — | No formula |
| I4 | — | No formula |
| J4 | — | No formula |
| H5 | — | No formula |
| I5 | — | No formula |
| J5 | — | No formula |
| H6 | — | No formula |
| I6 | — | No formula |
| J6 | — | No formula |
| H7 | — | No formula |
| I7 | — | No formula |
| J7 | — | No formula |
Change an actual source value
Edit actual source records or request interpretation. Use stored ASCII text for codes/queries and Pattern or Literal for interpretation; prices and units are finite numbers. Duplicate records return the first physical match.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Compare intentional pattern matching with literal identifiers, returning the first physical source record and preserving numeric zero.
THE QUICK ANSWER01
Compare intentional pattern matching with literal identifiers, returning the first physical source record and preserving numeric zero.
ƒx
=IFNA(VLOOKUP(IF(G2="Literal",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2,"~","~~"),"*","~*"),"?","~?"),F2),$A$2:$C$9,2,FALSE),"No match")For the example data
H2 11I2 1J2 AXH3 0I3 0J3 A*H4 11I4 1J4 AXH5 0I5 0J5 A*H6 55I6 5J6 001H7 No matchI7 No matchJ7 No match
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Patterns and identifiers answer different questions
The demo pattern A* selects AX first, while the literal A* request selects the second source row. Its zero price stays numeric zero. The matched-code column makes that difference visible.
First match follows the actual source order
Duplicate codes and overlapping patterns can match several records. This task returns the first one; it does not aggregate all matching prices or promise that source order is a business priority.
Escaping belongs to the request policy
Literal requests escape tildes before wildcard symbols. Pattern requests retain intentional matching syntax. Legacy VLOOKUP escapes and modern literal XLOOKUP modes are separate behaviors.
A fixed-looking answer is not reusable
All fields are checked against current source values, eight original populations and independent query/order/value probes. A correct amount with the wrong matched code fails the full report.
Read the full step-by-step walkthrough
- Read the request interpretation
The same A* request can mean any code starting with A or the complete code A*. Inspect F2:G3 before interpreting a returned number.
- Let a deliberate pattern remain a pattern
In Pattern mode, * matches any sequence and ? matches one character. A tilde escapes the following character in this legacy lookup. Return the first matching physical row, not every match.
- Escape the complete literal identifier
In Literal mode, double existing tildes before escaping * and ?. This preserves the spelling of codes such as A*, Q? and til~de. The source identifiers are not rewritten.
- Inspect the source key as well as values
H, I and J return three fields from the same selected record. A numeric zero price or units value is a valid found result; it must not be relabeled as missing.
- Keep missing records separate from invalid inputs
IFNA labels a missing match. It does not catch an invalid return-column reference or unsupported source types. Use explicit interpretation values and actual numeric measures.
- Fill, then change the source
Fill each of H2:H7, I2:I7 and J2:J7 from its own first-row formula. Reorder complete source rows, edit a source value or switch interpretation, and inspect every requested field again.
Things to watch
No request is escaped
A literal A* or Q? code can otherwise select an earlier different source record.
Zero is treated as missing
Use the actual #N/A state rather than a truthiness test.
The source moves with each request
Lock A2:C9 before filling request rows.
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.