FormulaLookups
Look up a value with INDEX and MATCH
The lookup that works in every Excel version ever shipped: INDEX pulls the result, MATCH finds the row, and the exact-match 0 keeps it honest.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →MATCH does the finding — it returns a row NUMBER, not the value itself — and INDEX uses that number to pull the actual result out of return_range; splitting the job this way is what lets the two ranges point anywhere, in either direction.
- →The third argument to MATCH, 0, forces an exact match. Leave it off and MATCH defaults to approximate matching against a sorted list, which returns a confidently wrong row on data that isn't sorted.
- →Because return_range and lookup_range are independent, the return column can sit to the LEFT of the lookup column — a layout VLOOKUP can never do without rearranging the sheet.
The fine print
- →Runs in every version of Excel back to the 1990s and in Google Sheets — the reason it's still worth knowing even with XLOOKUP available.
- →lookup_range and return_range must have the same number of rows, or the formula returns a #REF! or #VALUE! error.
- →A missing value returns #N/A, same as VLOOKUP; wrap the whole thing in IFERROR if a blank or a message is preferred instead.