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
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.