GS wrote:

Oops! got the args switched around for the lookup.
See correction below...

Another suggestion, to obviate need for nested IFs...

Store all possible criteria in a local scope named range with their
respective return values, then use a lookup function.

Example:

DefinedName: "Sheet1!RetVals"
RefersTo: $A$1:$C$2

Layout:
X | Y | Z
This | That | Other

DefinedName: "Criteria"
RefersTo: $A3 (with B3 selected so it's col-absolute, row-relative)

You can hide these 2 rows so your data can be setup normal.
Optionally, you could use 2 cols (A:B).

Criteria in colA, return values in colB with this formula...


=HLOOKUP(Criteria,RetVals,2,False)


Now you can have however many Criteria/RetVal pairs as you like all
working with 1 simple formula.<g


Thanks Gary, I'll try that flexible method at next opportunity.

--
Terry, East Grinstead, UK