Skip to content

INDEX and MATCH

A flexible lookup that works in every version of Excel and Google Sheets and can look left.

The formula

Argument separator
Excel and Google Sheets
=INDEX(Products!$C$2:$C$100, MATCH(A2, Products!$A$2:$A$100, 0))

How it works

  1. MATCH(A2, Products!$A$2:$A$100, 0)

    Finds the position of A2 in the code column. 0 means exact match.

  2. INDEX(Products!$C$2:$C$100, …)

    Returns the value at that position from the price column.

Example

A: CodeB: Result (price)
P-10112.50
P-3104.75
P-20589.00

Tips

  • Wrap it in IFERROR(…, "Not found") to hide #N/A.
  • Inserting columns in the table does not break it, unlike VLOOKUP's column number.