LOOKUP


Explanation of the LOOKUP function

There are 2 forms of the LOOKUP function:

  • The 1st is calling for vectors (a lookup_vector and a result_vector)
  • The 2nd is calling for an array (which will automatically determine the orientation of your data set and where to look for the result)

LOOKUP function array and vector forms

Explanation of the LOOKUP function (using VECTORS)

There are 3 different arguments when using this form of the LOOKUP function:

  1. lookup_value
  2. lookup_vector
  3. result_vector

LOOKUP vector example_1

1. lookup_value (boxed in blue)

This is the value you want to search for in your lookup_vector. You can either reference a cell (E3) like the example above or type your lookup value directly into the function. If you are searching for a string (text value), you need to put quotations before and after your lookup_value. If we did this in our example we would have to change E3 to “Forester” in the formula.

2. lookup_vector (boxed in red)

This is the range of data you want to use to search for your lookup_value. In our example our lookup_vector is B3:B7 because this is the range of data in which we will be able to find our lookup_value “Forester”.

Note: Make sure your entire data set is sorted in ascending order (the sort criteria should be based on the data within your lookup_vector) or else the LOOKUP function will not work properly. In the example above, our data is sorted in ascending order.

3. result_vector (boxed in purple)

This is range of data you want to return a result from. It must be the same size as your lookup_vector.

In this example, the result returned by the LOOKUP function (using VECTORS) is $28,000.

LOOKUP vector example_2

Explanation of the LOOKUP function (using ARRAY)

There are 2 different arguments when using this form of the LOOKUP function:

  1. lookup_value
  2. array

LOOKUP using array_1

1. lookup_value (boxed in blue)

This is the value you want to search for in your lookup_vector. You can either reference a cell (E3) like the example above or type your lookup value directly into the function. If you are searching for a string (text value), you need to put quotations before and after your lookup_value. If we did this in our example we would have to change E3 to “Forester” in the formula.

1. array (boxed in red)

This is where the array form of the LOOKUP function becomes a bit tricky.

  • It will automatically determine the orientation of your data and automatically search and return your result based on that.
  • The function will always search for your lookup_value within the “largest dimension” of your data set.
  • In our example, the vertical dimension of our array is 5 and the horizontal dimension is 2.
  • Our array is taller than it is wide.
  • This means the LOOKUP function will search for our lookup_value in the 1st column of our array (searching down the rows).

Note: the example we are using is the 2nd scenario below (“More rows than columns”).

How does LOOKUP (using ARRAY) figure out where to look for your lookup_value and how to find your result?

  • You have no control in terms of telling the function which row or column number to give you a result from (it will automatically bring the result from the last row or column depending on the orientation “dimensions” of your data)
  • You cannot control which column or row the function searches for your lookup_value (it will either choose the 1st row or 1st column depending on the orientation “dimensions” of your data)

how LOOKUP using ARRAY works

Note: Your data still has to be sorted in ascending order or else your LOOKUP function will not work properly.

In this example, the result returned by the LOOKUP function (using ARRAY) is $28,000.

LOOKUP using array_2

IMPORTANT (MUST READ) – You need to understand how the LOOKUP function works

NOTE: In order to use the LOOKUP function properly, your data set has to be sorted in ascending order.

In the example below we compare LOOKUP (using vectors) and VLOOKUP in 3 different scenarios.

Reminder: Formula Syntax

LOOKUP(lookup_value, lookup_vector, result_vector)
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

various lookup scenarios

how lookup function works in different scenarios

What are some differences between a LOOKUP and VLOOKUP function?

LOOKUP vs VLOOKUP in Microsoft Excel

* (1) LOOKUP (using ARRAY) returns the result from the last column or row depending on the orientation of your data.
* (2) LOOKUP (using ARRAY) automatically determines the orientation of your data. Read the section in this article covering “Explanation of the LOOKUP function (using ARRAY)” to see what I mean.

Some more examples of how a LOOKUP function (using ARRAY) works!

examples of LOOKUP array function

 

Go back to Excel Tutorials.