Tom’s Tutorials For Excel: Handling Multiple OR Lookup Criteria

Tom’s Tutorials For Excel: Handling Multiple OR Lookup Criteria

When you are faced with a LOOKUP task and the possibilities require too many nested “OR” operations, consider an array constant in your formula.

In the picture, a company is grouping its employees by the first initial of their last name.
The formula in cell B5 and copied down to cell B21 is
=LOOKUP(LEFT(A5,1),{"A","E","I","M","Q","U"},{"1","2","3","4","5","6"})

This is not an array formula — you enter it simply by pressing the Enter key. However, you *do* manually type in the curly braces that you see in the formula example. Array constants are memory storage units which in this case store a relationship between a letter of the alphabet and a Group number associated with that letter.

Note that the legend in the picture is just that, a guide for the user’s benefit. The formula itself does not refer to a lookup range, only to the corresponding associations between elements of the paired array constants.