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.
Leave a Reply