LOOKUPEX(value, in_range, out_range)

The LOOKUPEX function searches the list in_range, to find the item that is equal to value and returns the corresponding item from the list out_range. If the value is not found, LOOKUPEX returns #VALUE.

The in_range can be in any order (as contrasted with LOOKUP). LOOKUPEX works with both numbers and text.

For example:

LOOKUPEX(3, {1, 5, 4, 3, 2}, {10, 20, 30, 40, 50})  returns 40

LOOKUPEX("blue", {"red", "blue", "yellow"}, {3, 2, 1}) returns 2

The LOOKUPEX function produces the same result as combining INDEX and FINDEX, as follows:

INDEX(FINDEX(x, in_range), out_range)

See also:

Other lookup functions