Course outline / Lookup functions
Lesson 26 of 75 · Intermediate · 6 exercises
INDEX, MATCH and more lookups
Find values anywhere in a table, with the classic and flexible approaches.
What you'll practise
- Find a position with MATCH
- Fetch by position with INDEX
- Combine INDEX and MATCH
- VLOOKUP by name
- XLOOKUP for stock
- HLOOKUP across the top
Find a position with MATCH
MATCH tells you where something is in a list, as a position number. Use 0 as the last input to ask for an exact match.
=MATCH(what to find, list, 0)
In cell F2, find the position of "C30" in A2:A5.
Fetch by position with INDEX
INDEX does the reverse: give it a list and a position, and it returns the item at that position.
=INDEX(list, position)
In cell F2, return item 2 from the prices in C2:C5.
Combine INDEX and MATCH
Together, INDEX and MATCH make a flexible lookup: MATCH finds the row, and INDEX returns the value from that row in any column.
=INDEX(return list, MATCH(what to find, search list, 0))
In cell F2, find the price of "Chair": search for it in B2:B5 and return from C2:C5.
VLOOKUP by name
Remember that VLOOKUP always searches the first column of the table you give it, and counts columns from there.
In cell F2, find the price of "Lamp" using the table B2:D5, where Price is column 2. Ask for an exact match.
XLOOKUP for stock
XLOOKUP doesn't need column numbers, so it keeps working even if columns are moved around.
In cell F2, use XLOOKUP to find the stock of "Shelf", searching B2:B5 and returning from D2:D5.
HLOOKUP across the top
HLOOKUP is VLOOKUP turned on its side: it searches the top row of a table and returns a value from a row further down.
=HLOOKUP(what to find, table, row number, FALSE)
In cell F2, find "Stock" in the table A1:D5 and return row 3 of that table, which is the Desk row.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.