Lookup Function Array Function

Excel’s LOOKUP function is already a powerful tool for finding values in your data, but did you know it has a lesser-known array form? This feature allows you to search for values in a two-dimensional array, and it adapts its behavior based on the shape of the data. Let’s explore how this works and why it can be a game-changer for your Excel skills.

What Is the Array Form of LOOKUP?

In the array form, the LOOKUP function takes just two arguments:

1. lookup_value: The value you want to find.
2. array: A single two-dimensional array of data where you want to search.

Here's the formula structure:

LOOKUP(lookup_value, array)        

How Does It Work?

When you use the array form, Excel automatically adjusts how it searches based on the shape of your data:

• Tall or Square Array (More Rows than Columns or Equal): If your array is taller than it is wide (or square-shaped), LOOKUP behaves like VLOOKUP. It searches for the lookup value in the first column and returns a value from the last column of the array.
• Wide Array (More Columns than Rows): If your array is wider than it is tall, LOOKUP behaves like HLOOKUP. It searches for the lookup value in the first row and returns a value from the last row of the array.

Real-Life Examples

Let’s look at how this works in practice:

1. Vertical Array Example:

Suppose you have a list of data in columns B and C, and you want to find a value using a vertical array. The formula would look like this:

=LOOKUP(E4, B3:C9)        

In this case, LOOKUP searches for the value in E4 within the first column of the array (B3) and returns the corresponding value from the last column (C3).

2. Horizontal Array Example:

Now, if you’re dealing with data arranged horizontally, you can use the array form like this:

=LOOKUP(E4, B6:H7)        

Here, LOOKUP searches for the value in E4 within the first row of the array (C6) and returns the corresponding value from the last row (C7).

Why Use the Array Form?

The array form of LOOKUP is a flexible tool that adapts to different data structures without requiring you to change the function. It’s particularly useful when you’re working with data that might not fit the standard vertical or horizontal format. Plus, it’s a great way to streamline your formulas and reduce the need for multiple functions.

Conclusion

Understanding the array form of the LOOKUP function can add a new level of efficiency to your Excel workflows. It’s a simple yet powerful feature that can save you time and make your data retrieval tasks easier. As you continue to refine your Excel skills, experimenting with the array form of LOOKUP is definitely worth your while.

Happy Excel-ing

For more update:

Please join our What's App channel by clicking Here