The LOOKUP Function is a powerful tool that allows excel users search for a specific value in a single column or row range of cells and then return a value from another single column or row range. The LOOKUP function is mostly deployed for retrieving data from large data sets or lookup tables. Lookups with the LOOKUP function can be done in 2 forms namely the vector form and the array form.
The vector form is the more common of the two whereby you look up a value in a single vertical or horizontal range (like B3:B10 or B3:G3), while specifying the result vector. The array form of the LOOKUP function on the other hand allows you select multiple arrays for your lookup / result vector and works basically like a VLOOKUP or HLOOKUP function where it looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.
Getting confused? Don’t worry, we would treat examples on both forms of the lookup function and also highlight key things to note when using this function. Let’s kick things off with the Syntax and argument of the LOOKUP function.
Syntax & Argument
The syntax of the LOOKUP function is as follows:
=LOOKUP(lookup_value, lookup_vector, [result_vector]).
The LOOKUP Function ha 3 argument, 2 are required and the third is optional:
Lookup value: This is the value to search for within the lookup array.
Lookup vector: This represents the range of cells than contains the values to be searched.
result_vector: This is the optional argument which represents the range of cells from which you want to retrieve the corresponding value. If this is omitted by the user, the function will return the matching value from the lookup vector.
That’s it for the arguments of the LOOKUP function. Now let’s take a pitt stop at the usage note section before moving on to some examples.
Usage Notes
- The LOOKUP function works best when looking up numerical values and it assumes that the values in the lookup vector are sorted in ascending order.
- The LOOKUP function retrieves an approximate matched value rather than the position of the value in the range.
- To get an accurate approximate matched value, it is important to sort your data in ascending order using the lookup vector column. This is because the function sorts the values in the lookup vector when calculating, and then retrieves the value on the position it calculates in the sorted lookup vector.
- If the LOOKUP function cannot find a match, it returns a #N/A error value
- If there are multiple occurrences of your lookup value in the lookup vector range, the LOOKUP function returns the first occurrence.
- If the result vector argument is omitted, the LOOKUP Function
LOOKUP Function Examples
We would use a data set that contains the market capitalization values of various countries in the world for our first few examples.
Basic Example
In this section, we are going to return an approximate Market Cap match for our lookup value. To achieve this, we omit the result vector argument. If the result vector argument is omitted, the function will return the corresponding value from the lookup vector.
From the above, we can see that our lookup value is 2000 and we need to find the value of Market Cap in our data set that is closest to this value. We only need to type in the lookup formula below to return this value.
The next example using the dataset is to lookup the same value and then retrieve their corresponding country name:
Yes, the closest market Cap value (1867) can obviously be used to return the Country name, however we are only interested in looking up the Country name with our original lookup value in cell I4 (2,000). For us to do this, we need to include the result vector range.
Last Non-Empty Cell
Now let’s add one more value to lookup. This time around, it is not a direct value but a calculated value based on a certain condition which is the last non-empty cell. We would be retrieving the last country entry in our list. Of course, we can see visually that it is Geece, however we want to use the LOOKUP Function to return this same value.
Our formula to achieve our target is as follows:
Latest Revenue
In this section, we have the quarterly performance of a company till the Gross margin level. We would use the LOOKUP function to retrieve the most recent value for one of the line items. Let’s take a quick look at our data below.
The most recent revenue is the 4th quarter’s revenue, and our formula should return 76,533.
In the above formula, we named Cells C4:C15 line and cells E4:E15 financials. Please see how guide on named ranges for more information on how to do this.
LOOKUP Array Form
At the beginning of this post, we talked about the array form of the lookup function. This is very useful if you are in a hurry and need an intuitive lookup. Intuitive in the sense that the function automatically adjusts to the dimensions provided. With this form, only two arguments are needed namely the lookup value and a single two-dimensional array.
In our example for this section, we have 2 arrays (vertical and horizontal) showing score ranges and their respective range. We would illustrate how to use the LOOKUP function for both types of arrays.
For our score in cell B14, we would use the LOOKUP function to return its equivalent grade. The formula to use is as follows:
On to the next one. For our horizontal range, we select F4 to K5 as our array. Our formula for this can be seen below:
