INDEX + MATCH

Two-function combination that returns a value from any position in a range. More flexible than VLOOKUP — can look in any column, not just the first.

Syntax

smartsheet-mastery
=INDEX({return_range}, MATCH(lookup_value, {lookup_range}, 0))

Example

smartsheet-mastery
=INDEX({Price Range}, MATCH([Product ID]@row, {Product ID Range}, 0))
=INDEX({PM Name Range}, MATCH([Project ID]@row, {Project ID Range}, 0))