Array shortcut

Recommended Posts

Array shortcut

Many Excel users bristle when you bring up the subject of arrays. The term array conjures up images of unwieldy syntax and complicated mathematical concepts. Help your students or end users overcome their objections with this practical application of array constants that can help save keystrokes.

To illustrate one use of array constants, consider how you use the OR function to evaluate the contents of a given cell. The formula =OR(A1=4,A1=5,A1=6) evaluates to true if A1 contains 4, 5, or 6.

You typically use the OR function in conjunction with the IF function, in the form =IF(OR(A1=4,A1=5,A1=6),true_result,false_result). If A1 contains 4, 5, or 6, the IF function returns the true_result. If A1 contains any other value, the formula returns the false_result.

By using an array constant, you can save yourself some keystrokes and decrease the chance of typographical errors.

The expression =OR(A1={4,5,6}) yields the same result as the expression =OR(A1=4,A1=5,A1=6). If you want to add 8 to the list of possible values that make the OR function evaluate to True, you'd just add a comma and the number 8 in the form =OR(A1={4,5,6,8}).

When using arrays, keep in mind that the array constant, by definition, works only with values. If you need to compare the entry in A1 to other cells or named ranges, you must use the A1= approach.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.