The last argument is omitted
The default approximate search is a different contract and can misread unsorted headers.
Look up fields across a horizontal source
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter C8 and fill C8:C11. Each A8:A11 query is a literal text identifier matched case-insensitively against B1:G1; *, ? and ~ are identifier characters, not patterns. Return the value from table-relative row B8:B11 of B1:G4, where 2 is Net amount, 3 Days elapsed and 4 Adjustment. Duplicate headers use the leftmost matching column. Missing headers return Not found, while a found numeric zero stays zero. Headers and queries are nonempty ASCII text up to 40 characters, including leading zeroes; source measures are finite numbers within ±1,000,000 and return rows are integers 2–4. Keep all source columns and request rows in place. Exact matching is explicit; the header row need not be sorted.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: C8, C9, C10, C11. Browser calculations may differ from your Excel version.
Inspect current stored results and formulas
| Cell | Current value | Applied formula |
|---|---|---|
| C8 | — | No formula |
| C9 | — | No formula |
| C10 | — | No formula |
| C11 | — | No formula |
Change an actual source value
Keep headers and queries as nonempty ASCII text up to 40 characters, including numeric-looking IDs and literal *, ? or ~. Header duplicates deliberately return the first match, ignoring ASCII letter case. All three measure rows contain finite stored numbers within ±1,000,000. Each return-row control is an integer 2–4 relative to the horizontal table, not the request row.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Match literal identifiers across a header row and return the requested measure, preserving first-match, zero and missing policies.
THE QUICK ANSWER01
Match literal identifiers across a header row and return the requested measure, preserving first-match, zero and missing policies.
ƒx
=IFNA(HLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"~","~~"),"*","~*"),"?","~?"),$B$1:$G$4,B8,FALSE),"Not found")For the example data
C8 12C9 2C10 0C11 Not found
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Horizontal means the first row supplies the keys
The return-row selector belongs to the four-row table. Request row 8 can ask for table row 2. Reordering entire source columns is valid; the key and all three measures must move together.
Literal text identity has a declared policy
This task ignores ASCII letter case but preserves every other character and leading zero. HLOOKUP exact mode supports wildcard syntax, so identifiers containing pattern characters are escaped before searching.
First duplicate and missing results are separate choices
The leftmost duplicate is authoritative here. IFNA labels a missing key without replacing a numeric zero. This is a first-match lookup, not an aggregation of duplicate columns.
Read the full step-by-step walkthrough
- Read the horizontal table and request controls
HLOOKUP searches the first row across columns. The return row counts down inside B1:G4, not down from the request. Inspect a query, its row selector and the requested result.
- Escape the literal identifier before lookup
Escape existing tildes first, then asterisks and question marks. Otherwise A* can match AB and Q? can match QX. Source identifiers are not rewritten.
- Use explicit exact matching and a fixed source
FALSE selects exact matching across the unsorted header row. Lock B1:G4 while the query and return-row controls move with each request. A duplicate returns the leftmost match, ignoring ASCII case.
- Keep found zero separate from a missing header
IFNA handles the missing-header result only. A found zero remains a stored number. Unsupported row selectors are rejected by the source editor, rather than hidden under a catch-all label.
- Fill requests without moving the table
Fill C8:C11. The four query/return-row controls move relative to each request; the source rectangle remains fixed. Then change an actual measure or row selector and inspect the recalculated result.
Things to watch
A* is treated as a pattern
The task asks for the literal A* identifier, which differs from AB. Escape it first.
Zero is shown as Not found
A found zero is a valid stored numeric value. Missing-key handling must not use a truthiness check.
About this example
Original bounded rank/horizontal lookup tasks; browser and current workbook checks are separate from native-client recalculation/rendering/edit-save certification.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.