INDEX MATCH: find a row, then return its value

A worked lookup you can audit

Use this Products catalog. On Orders, set A2 to P-204. Your goal is to return its price without counting columns inside a table.

=MATCH(A2,Products!$A$2:$A$4,0)

MATCH returns 2: P-204 is the second item in A2:A4. This is a relative position, not worksheet row 2. The final 0 means exact matching; omitting it changes MATCH’s behavior.

=INDEX(Products!$C$2:$C$4,2)

INDEX returns 12.00, the second value in the price range. Replace the literal 2 with MATCH to connect the steps:

=INDEX(Products!$C$2:$C$4,MATCH(A2,Products!$A$2:$A$4,0))

Return a value to the left

Suppose Orders!B2 contains Stapler and you need its ID. Keep the same relative row alignment, but switch the search range to names and the return range to IDs.

=INDEX(Products!$A$2:$A$4,MATCH(B2,Products!$B$2:$B$4,0))

The result is P-204. INDEX does not require the result column to be to the right of the search column. This makes the pair useful for shared workbooks that cannot use XLOOKUP.

Prevent a correct-looking wrong answer

If MATCH searches A2:A4 but INDEX returns C3:C5, position 2 points at a different record. Start and end both ranges on corresponding rows. Fix both catalog ranges with $ so copying the formula does not shift one of them. Keep only the order’s lookup cell relative if each order has a different ID.

For a missing ID, MATCH returns #N/A and INDEX passes that error through. Diagnose the missing record first. If absence is expected, you can wrap the completed formula in IFNA:

=IFNA(INDEX(Products!$C$2:$C$4,MATCH(A2,Products!$A$2:$A$4,0)),"ID missing")

This wrapper is a manual example and is not an output supported by our VLOOKUP generator. Microsoft’s MATCH reference explains match types; use 0 for these ID lookups.