**LOOKUP** has two forms: an array form and a vector form. I suppose I had better explain the jargon:

- an array is a collection of cells consisting of at least two rows and at least two columns
- a vector is a collection of cells across just one row (row vector) or down just one column (column vector).

The diagram should be self-explanatory:

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 l**ookup_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.

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- l
**ookup_vector**is the range that contains only one row or one column **[result_vector]**is optional – if ignored,**lookup_vector**is used – this is the where the result will come from and must contain the same number of cells as the**lookup_vector**.

Like the default versions of **HLOOKUP** and **VLOOKUP**, **lookup_value** must be located in a range of strictly ascending values, ie where each value is larger than the one before and there are no duplicates.

So why do I advocate for **LOOKUP** when I said don’t use the other functions just recently? Well, it’s because it is simpler to use, doesn’t rely on row or index column numbers and allows modellers to create inputs that do not need to be specified for all periods modelled. Let me demonstrate with the following example (the full set of examples may be found in the attached Excel file):

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

Do you see the data table in cells **F12:K13 **above? The value in the final cell of the first row is actually “2028” not “2028+”. 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 2028.

The formula 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 which is greater than 2028, the 2028 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.

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

Whilst the formula 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 there are the same number of elements 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 strict 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:

**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!

## Word to the Wise

Whilst **XLOOKUP** is powerful in Excel 365 and **INDEX MATCH** is a great all-rounder in general, the power of **LOOKUP** is that it doesn’t return #N/A values necessarily when it can’t find something. This can be invaluable when you are forecasting for many periods, but don’t wish to add inputs for more than a few. Consequently, I recommend **LOOKUP** in forecasting models where you are looking up dates (as these are always ordered) and you require the last available value to be returned.

##### Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.