Looking up only F2
The remaining three requests still require full result rows.
Return three fields for a batch of lookup queries
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. For each of the four query keys F2:F5, return Team, Price and Stock from the six source records A2:D7. Keep query order and repeated requests. Match full literal text without case distinction; * and ? are literal characters, not patterns. If several source keys match, use the first physical row. If a query has no match, return Not found in each of its three output cells. Retain numeric zero and signed values. All keys and teams are nonempty ASCII text up to 40 characters; Price and Stock are finite numbers within ±1,000,000.
Change an actual source value
Choose a cell and its stored type, apply the value, then check the complete report again. A text key can contain literal punctuation. Ready values are actual Booleans. Invalid source types leave the worksheet unchanged.
This draft matches the current cell.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:I8. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Look up four requested keys in order, preserve repeated requests and return the first exact record with an explicit missing policy.
THE QUICK ANSWER01
Look up four requested keys in order, preserve repeated requests and return the first exact record with an explicit missing policy.
ƒx
=MAKEARRAY(ROWS(F2:F5),3,LAMBDA(r,c,XLOOKUP(INDEX(F2:F5,r),A2:A7,INDEX(B2:D7,0,c),"Not found",0)))| Team | Price | Stock |
|---|---|---|
| North | 12 | 0 |
| South | 0 | -2 |
| Not found | Not found | Not found |
| East | -5 | 4 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Read the first requested batch
A* returns North,12,0 from the first matching source record; a later a* does not replace it. B? returns South,0,-2. Missing yields three Not found cells. 000 returns East,-5,4.
Separate row and field iteration
The output is the cross-product of four query positions and three requested fields. Explicit coordinates keep both dimensions visible instead of relying on one lookup to infer a two-dimensional cross-product.
Preserve meaning and types
A repeated query repeats its first source result. Zero Price or Stock is numeric data, while Not found is text. This task does not merge duplicate source records or sum their values.
Read the full step-by-step walkthrough
- Keep the query population separate
F2:F5 has four requested keys. Duplicate requests stay separate output rows, and a missing key still occupies its requested row.
- Generate every output coordinate
MAKEARRAY creates four rows and three columns. Its LAMBDA receives one row coordinate r and one field coordinate c.
- Choose the current requested key
INDEX(F2:F5,r) supplies the key for that output row. Every field in the same output row uses that same key.
- Choose the current source field
INDEX(B2:D7,0,c) supplies the complete Team, Price or Stock column without moving it away from its source keys.
- Apply exact first-match lookup
XLOOKUP uses match mode zero. It searches from the first source row, treats punctuation literally and returns the chosen field.
- Retain the missing query row
The not-found argument returns text for each missing field. It does not turn a missing price into numeric zero or delete the requested row.
Things to watch
Returning the last duplicate
This task explicitly uses the first physical source row.
Enabling wildcard matching
A* and B? are full literal identifiers here.
Returning zero for a missing record
Missing text must remain distinct from a real numeric zero.
About this example
Original bounded arrays, complete typed outputs and current/changed-source browser calculations; native recalculation and edit/save remain separate.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.