INDEX / MATCH

The classic power-user lookup combination. INDEX returns a value from a position; MATCH finds the position. Together they outperform VLOOKUP: works in any direction, column-insertion-proof, and handles complex multi-criteria lookups.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

=INDEX(PriceTable, MATCH(B2, RowHeaders, 0), MATCH(C2, ColHeaders, 0))

=INDEX(SalesData[Amount], MATCH(MAX(SalesData[Amount]), SalesData[Amount], 0))