# Things are looking up with this Excel function

What’s the best choice when faced with an array of choices for finding specific data in a financial model? Consider this vector analysis.Regular readers may recall I took **VLOOKUP **and **HLOOKUP **to task recently, identifying potential pitfalls in these two lookup functions. Conspicuous by its absence, **LOOKUP** may seem a less versatile function upon first glance, but it is quite useful for modelling. Let’s take a closer look.

**LOOKUP** requires the data to be “looked up” to be in ascending order, but it doesn’t have to be *strict* ascending order. For example, the sequence *1, 2, 3, 3, 3, 4, 77, 77, 78* would be fine. It then hunts out the largest value less than or equal to what it is looking for. So with duplicates, this means the final occurrence of the value, which can be really useful when grouping periods in modelling. In fact, **LOOKUP** is often the optimal function to use when you are looking up dates, as they are always in ascending order.

**LOOKUP **has two syntax forms: an *array* form and a *vector* form. Let me explain the jargon:

- An
**array**is a collection of cells consisting of at least two rows and at least two columns, as illustrated by the yellow box in the diagram below. - A
**vector**is a collection of cells across just one row (row vector) or down just one column (column vector), as illustrated by the green boxes in the diagram below.

The array form of **LOOKUP** 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 same array:

**LOOKUP(lookup_value,array)**

Where:

**lookup_value**is the value that**LOOKUP**searches for in an array. The**lookup_value**argument can be a number, text, a logical value, or a name or reference that refers to a value.**array**is the range of cells that contains text, numbers, or logical values that you want to compare with**lookup_value**.

The array form of **LOOKUP** is very similar to the **HLOOKUP** and **VLOOKUP** functions. The difference is that **HLOOKUP** searches for the value of **lookup_value** in the first row, **VLOOKUP** searches in the first column, and **LOOKUP** searches according to the dimensions of **array**.

If **array **covers an area that is wider than it is tall (ie,* *it has more columns than rows), **LOOKUP** searches for the value of **lookup_value** in the first row and returns the result from the last row. Otherwise, **LOOKUP** searches for the value of **lookup_value** in the first column and returns the result from the last column instead.

For example, when there are more columns than rows:

**LOOKUP **will seek the “5” in the first row and return the corresponding value (“220”, highlighted) in the bottom row. However, as soon as there are at least as many rows as columns, **LOOKUP **will “flip” and seek the “5” in the first column and return the corresponding value (as shown below with “230” highlighted) in the final column.

You should not be held hostage to an end user inserting rows and/or columns and messing up your formulas, so the array form is not recommended generally.

The alternative form is the vector form:

**LOOKUP(lookup_value,lookup_vector,[result_vector])**

The **LOOKUP** function vector form syntax has the following arguments:

**lookup_value**is the value that**LOOKUP**searches for in the first vector.**lookup_vector**is the range that contains only one row or one column.**[result_vector]**is optional — if ignored,**lookup_vector**is used — this is where the result will come from and must contain the same number of cells as the**lookup_vector**.

As mentioned above, like the default versions of **HLOOKUP **and **VLOOKUP**, **lookup_value** must be located in a range of ascending values, ie, where each value is greater than or equal to the one before. If this rule is followed, **LOOKUP** will return the value occurring to the *final* occurrence of the **lookup_value** (whereas **MATCH **would return the first occurrence).

To explain why I think **LOOKUP **is so useful with dates, look no further than referencing model inputs. You may have a model that forecasts for 20 years annually or 10 years monthly, etc.* *That’s a lot of inputs to enter. Isn’t there a better way to do it where the end user doesn’t have to put in inputs in every period, but can just assume they are the same as the last period after a while? This is where **LOOKUP** comes into its own.

Yes, **LOOKUP** is simpler to use and doesn’t rely on row or index column numbers. But there’s more to it than that: It allows modellers to create inputs that do not need to be specified for all periods modelled. That makes models smaller, more efficient, and easier to follow.

Let me demonstrate with the following example (the full set of examples may be found in this Excel file):

Imagine you have an annual model forecasting for many years into the future. Creating inputs can be time-consuming if data have to be entered on a period-by-period basis. But there is a shortcut.

Consider the data table in cells **F12:K13** above. The value in the final cell of the first row is actually the numerical value “2020” not the text value “2020+”. It appears that way due to custom number formatting (**CTRL + 1**):

The syntax “0+” adds a plus sign to the number although Excel still reads the value as 2020.

The formula in the first example, **LOOKUP(G$19,$G$12:$K$13)**, uses the array version of **LOOKUP**, looking up the year in the first row of the data table and returning the corresponding value from the final row. When a year is selected that is greater than 2020, the 2020 value is used as **LOOKUP** seeks out the largest value less than or equal to the value sought. Therefore, we don’t need to have lengthy data tables — once we assume inputs will be constant thereafter, we can just curtail the input section.

As mentioned earlier, using the array form of **LOOKUP** is dangerous though. What if someone inserts rows? The lookup will “flip” to look at the first and last columns instead, which is not what is required. Using the vector form is safer:

Whilst the formula shown above, **LOOKUP(G$19,$G$12:$K$12,$G$13:$K$13)**, contains one more argument, the formula is more stable. Further, the **lookup_vector **and the **result_vector** do not need to be in the same worksheet or even the same workbook. In fact, as long as the same number of elements is in each, one can be a row vector and the other a column vector.

**LOOKUP** is very useful when the **lookup_vector** contains data in ascending order. Where do we find this? Dates in time series. **LOOKUP** is very useful for financial modelling/forecasting. Just be careful though. Consider the following scenario, where the Assumptions start with the year 2016 but we still want Results for the years 2013–2015 (let’s assume that we know the Assumptions for years before 2016 were also 3%):

Here, the same formula generates an **#N/A** error. This is because the date is smaller than the smallest value in the data range. **LOOKUP** is not quite clever enough to use the first value unprompted, but a simple tweak of the formula will suffice:

Here, the formula has been modified to:

**=****IF(G$19<$G$12,$G$13****,LOOKUP(G$19,$G$12:$K$12,$G$13:$K$13))**

The added **IF** statement checks to see if the year is smaller than the first year in the data table and if so, returns the first result. Simple!

It is with this final modification — in its vector form — that I usually use **LOOKUP** to return values for certain time periods where I do not want to have an input for each period modelled. Very useful!

In case you aren’t convinced, let’s finish with the following example:

Here, I have monthly financial data for Sales (from the income statement) and for Net Assets (from the balance sheet). To summarise the data quarterly, row 22 uses a **SUMIF **function to sum the *sales* data *if* it is in that particular quarter. Modellers perform similar calculations day in and day out:

**=SUMIF($H$11:$Y$11,H$20,$H$13:$Y$13)**

However, row 24 shows the closing balance for Net Assets in each quarter. Here, I regularly see a vast array (pun intended) of megaformulas that Tolstoy would have been proud of. We don’t wish to sum the Net Assets amounts for all the months included in the quarter — we just want the final balance. This is something **LOOKUP** was born to do. The function in cell **H24**:

**=LOOKUP(H$20,$H$11:$Y$11,$H$15:$Y$15)**

is a very simple alternative to the nightmare calculations performed regularly elsewhere.

*— Liam Bastick**, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of *Introduction to Financial Modelling*. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an *FM* magazine senior editor, at Jeff.Drew@aicpa-cima.com.*