Lookup and Reference Functions
Lookup functions find a value in a table and return related data. They are the heart of office Excel work.
VLOOKUP and HLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Meaning |
|---|---|
| lookup_value | What to find. |
| table_array | The table. The value must be in its first (leftmost) column. |
| col_index_num | Which column to return, counted from the first column of the table (1, 2, 3 ...). |
| range_lookup | FALSE or 0 = exact match. TRUE or omitted = approximate match (data must be sorted ascending). |
| Formula | Result |
|---|---|
=VLOOKUP("Eram",A2:D9,3,FALSE) | 88 (Eram's marks) |
=VLOOKUP("Eram",A2:D9,2,FALSE) | Srinagar |
=VLOOKUP("Zoya",A2:D9,3,FALSE) | #N/A (not found) |
=IFERROR(VLOOKUP("Zoya",A2:D9,3,FALSE),"Not found") | Not found |
=VLOOKUP("Sri*",A2:D9,1,FALSE) | Wildcard match (needs text starting Sri) |
Approximate match for grades. Put this grade table in F2:G7, sorted ascending by marks:
| F | G |
|---|---|
| 0 | F |
| 40 | D |
| 50 | C |
| 60 | B |
| 75 | A |
| 90 | A+ |
=VLOOKUP(78,$F$2:$G$7,2,TRUE) → A. Excel returns the largest value that is less than or equal to 78, which is 75. This is the clean alternative to a long nested IF.
HLOOKUP works the same way but searches the top row and returns from a row: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
INDEX and MATCH
| Function | Syntax | Example | Result |
|---|---|---|---|
| MATCH | =MATCH(lookup_value, lookup_array, [match_type]) | =MATCH("Eram",A2:A9,0) | 5 (position, not value) |
| INDEX | =INDEX(array, row_num, [col_num]) | =INDEX(A2:D9,5,3) | 88 |
| INDEX + MATCH | =INDEX(return_range, MATCH(value, lookup_range, 0)) | =INDEX(A2:A9,MATCH(92,C2:C9,0)) | Charu (looks left) |
| Two-way lookup | =INDEX(table, MATCH(row_value, rows, 0), MATCH(col_value, cols, 0)) | Row and column both found |
MATCH types: 0 = exact; 1 = largest value less than or equal (data sorted ascending, the default); -1 = smallest value greater than or equal (data sorted descending).
Top scorer: =INDEX(A2:A9,MATCH(MAX(C2:C9),C2:C9,0)) → Charu.
XLOOKUP (Excel 2021 and Microsoft 365)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP("Eram",A2:A9,C2:C9,"Not found")→ 88- Looks left or right, defaults to an exact match, needs no column number, and has a built-in not found message.
- match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. search_mode: 1 first to last, -1 last to first.
- XMATCH is the matching partner of MATCH.
VLOOKUP, INDEX-MATCH and XLOOKUP compared
| Feature | VLOOKUP | INDEX + MATCH | XLOOKUP |
|---|---|---|---|
| Look to the left | No | Yes | Yes |
| Default match | Approximate | Set by you | Exact |
| Breaks if a column is inserted | Yes (fixed column number) | No | No |
| Available in | All versions | All versions | 2021 and 365 |
Other reference functions
| Function | Example | Result |
|---|---|---|
| LOOKUP | =LOOKUP(78,F2:F7,G2:G7) | A (needs sorted data) |
| CHOOSE | =CHOOSE(2,"Low","Medium","High") | Medium |
| ROW / COLUMN | =ROW(C5) / =COLUMN(C5) | 5 / 3 |
| ROWS / COLUMNS | =ROWS(A2:D9) / =COLUMNS(A2:D9) | 8 / 4 |
| ADDRESS | =ADDRESS(2,3) | $C$2 |
| OFFSET | =OFFSET(A1,2,1) | Value in B3 (2 rows down, 1 column right) |
| INDIRECT | =INDIRECT("C"&5) | Value in C5 (address built from text) |
| TRANSPOSE | =TRANSPOSE(A1:A4) | Turns a column into a row |
| HYPERLINK | =HYPERLINK("https://jkedusphere.com","Open") | A clickable link |
OFFSET and INDIRECT are volatile: they recalculate every time anything changes.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.