Broad Strokes

Well implemented lookups make models significantly more flexible

Lookup formulas are one of the greatest features of Excel. When working across more than one database, these formulas can be used to translate between the two and retrieve relevant data automatically (e.g., corresponding to a time period, category, etc.). When these are well implemented, it will add a new level of agility to the model.

Usage and Style

There are four types of lookup formulas in Excel:
INDEX(MATCH)), LOOKUP(), VLOOKUP(), and HLOOKUP().

Of these four, I strongly recommend using INDEX(MATCH()) for almost everything. Occasionally, LOOKUP() comes in handy when using array formulas. Although VLOOKUP() and HLOOKUP() are not recommended, this article digs into the alternatives as well. Because these are commonly used formulas, it’s advisable to adhere to established technical conventions if you’re working in an existing/shared workbook. You may also want to avoid the use of array formulas for client-facing applications.

When building an application from scratch, however, there are four good reasons to stick with the preferred INDEX(MATCH()) and LOOKUP() formulas:

Under most circumstances, the combination of INDEX() and MATCH() formulas offers more functionality than LOOKUP(), VLOOKUP(), or HLOOKUP(), with consistent syntax in all applications.
With a little help from the TRANSPOSE() array formula, INDEX(MATCH()) can perform two-dimensional lookups on a table, providing the combined functionality of VLOOKUP() and HLOOKUP().
VLOOKUP() uses a hard-coded integer to specify the offset of the lookup column relative to the reference column. This means that VLOOKUP() will not read the intended data if you add or delete a column between the reference and lookup columns. Ditto for HLOOKUP() and its references to rows.
INDEX(MATCH()), VLOOKUP(), and HLOOKUP() do not play nicely with array formulas, which leaves only LOOKUP() for this particular situation.

When using array formulas, the lesser-known LOOKUP() is the only option available. This formula is similar to VLOOKUP() but offers the advantage of using direct cell references to assign the reference and lookup columns in the formula. Unfortunately, LOOKUP() lacks the ‘exact match’ option that is available in all three of the alternatives, so you’ll need to make sure that your reference column is sorted in ascending numerical/alphabetical/chronological order.

Syntax

Click the following buttons to read more about each formula.

INDEX(MATCH())

Looks up data based on column and/or row headings – recommended for most applications

LOOKUP()

Use if necessary: Looks up tabulated data based on row headings – data must be sorted.

VLOOKUP()

Not recommended: Looks up tabulated data based on row headings – less robust than INDEX(MATCH()).

HLOOKUP()

Not recommended: Looks up tabulated data based on column headings – less robust than INDEX(MATCH()).

This combination of INDEX() and MATCH() formulas allows you to retrieve data from a column (and/or row) corresponding to the position of a term in a lookup column (and/or row). Let’s take a look at each of these formulas before we put them together:

INDEX()

INDEX(array, row_num, [column_num]) returns a value from the array at the specified row (and/or column) position.

array: the range from which a value will be retrieved
row_num: the row address from which a value will be retrieved (where the top row in the range is 1)
column_num: the column address from which a value will be retrieved (where the leftmost column in the range is 1)

In the INDEX(MATCH()) combination, the INDEX() formula performs the data retrieval – in order to get the row_num and column_num numbers based on a lookup in another column, we’ll turn to the MATCH() formula.

MATCH()

MATCH(lookup_value, lookup_array, [match_type]) returns a number corresponding to the vertical position of the lookup_value in the lookup_array.

lookup_value: the value that will be looked up
lookup_array: the range in which the lookup_value should be sought
match_type (optional): can be set to 1, 0, or -1

The following table describes the options available for the match_type parameter.

match_type	Behaviour
1	This value instructs MATCH() to search for the first match that is less than or equal to the lookup_value. Even if the lookup_value is text, the lookup_array must be sorted in ascending numerical/alphabetical/chronological order for the formula to work correctly.
0	This method searches for an exact match in the lookup_array. It does not matter if the lookup_array is sorted.
-1	This method searches for the first match that is greater than or equal to the lookup_value. The lookup_array must be sorted in descending numerical/alphabetical/chronological order for this operation to work correctly.

All said, the choices for match_type are the same as those available in the more familiar VLOOKUP() formula, except that MATCH() adds the ability to search for a match that is greater than or equal to the lookup_value.

Now that we’ve looked at the component pieces, let’s put it all together in a generalized formula.

INDEX(MATCH()) | Basic form

INDEX(array, MATCH(lookup_value, lookup_array, match_type))

This is the most basic form of INDEX(MATCH()). As it appears above, this formula performs an identical function to VLOOKUP(), except that the data retrieval and lookup ranges are stated as direct cell references. This ensures that your formulas will link to the correct data, even if you insert/delete columns in the worksheet. Improving upon VLOOKUP(), this configuration also allows you to look up data in a column to the left of the lookup column.

In order to access the full two-dimensional lookup functionality of this formula, the optional third term in the INDEX() formula can be applied as follows:

INDEX(MATCH()) | Extended form

INDEX(array, MATCH(lookup_value1, rowheadings, match_type), [MATCH(lookup_value2, TRANSPOSE(columnheadings), match_type)])

The optional third term adds the horizontal searching capability of HLOOKUP(). For this instance of MATCH() to work, you must press ctrl-shift-enter after inputting the formula. Within this term, the TRANSPOSE() array formula is used to convert the lookup_columnheadings from a horizontal range into a vertical range. This operation is necessary to make the horizontally-oriented lookup array compatible with the vertically-oriented MATCH() formula. If you’re not familiar with array formulas, check out the array formula section of this site.

To use INDEX(MATCH()) in the place of an HLOOKUP(), set the first term (array) to the specific row that you want to read from, set the second term to 1, and use the third MATCH() term to perform a lookup on the column headings as shown above.

This formula allows you to retrieve data from a column corresponding to the position of a matching term in a lookup column.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches for the lookup_value in the leftmost column of table_array using the method specified by range_lookup. When the formula finds a match, it returns a value from another column (identified by col_index_num) in the same row as the match.

lookup_value: the value that will be searched for
table_array: the range where data for the lookup and retrieval is located (the lookup will be performed on the leftmost column in this range)
col_index_num: the column from which a value will be retrieved (where the leftmost column in the range is 1)
range_lookup: the lookup type to be employed – can be set to TRUE or FALSE

The following table describes the options available for the range_lookup parameter.

range_lookup	Behaviour
TRUE	This value instructs VLOOKUP() to search for the first match that is less than or equal to the lookup_value. Even if the lookup_value is text, the table_array must be sorted in ascending numerical/alphabetical/chronological order for the formula to work correctly.
FALSE	This method searches for an exact match in the table_array. It does not matter if the table_array is sorted.

Not Recommended: This formula does not work with array formulas and requires you to specify the data retrieval column as a hard coded number relative to the lookup column. This formula will also not let you look up data to the left of the lookup column.

This formula allows you to retrieve data from a row corresponding to the position of a matching term in a lookup row.

HLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches for the lookup_value in the top row of table_array using the method specified by range_lookup. When the formula finds a match, it returns a value from another row (identified by col_index_num) in the same column as the match.

lookup_value: the value that will be searched for
table_array: the range where data for the lookup and retrieval is located (the lookup will be performed on the top row in this range)
col_index_num: the column from which a value will be retrieved (where the top row in the range is 1)
range_lookup: the lookup type to be employed – can be set to TRUE or FALSE

The following table describes the options available for the range_lookup parameter.

range_lookup	Behaviour
TRUE	This value instructs HLOOKUP() to search for the first match that is less than or equal to the lookup_value. Even if the lookup_value is text, the table_array must be sorted in ascending numerical/alphabetical/chronological order for the formula to work correctly.
FALSE	This method searches for an exact match in the table_array. It does not matter if the table_array is sorted.

Not Recommended: This formula does not work with array formulas and requires you to specify the data retrieval row as a hard coded number relative to the lookup row. This formula will also not let you retrieve data from above the lookup row.

Examples

The excel file embedded below demonstrates the use of all four formulas to perform the same set of tasks. Notice that it is possible to use each formula to do almost anything, although only the INDEX(MATCH()) option uses consistent syntax across every application.

All of the example formulas listed here look up data from the named table at the top of the sheet.

Download this Workbook [12.20 KB]

Note that the examples here are not exhaustive – it’s possible to add other formulas (like TRANSPOSE(), MATCH(), OFFSET(), INDIRECT(), etc.) that will allow most of these lookup formulas to perform any stated function. The intent here is to compare how each formula would reasonably be used in real-world applications.

ing the

break

mould

model

model

Broad Strokes

Well implemented lookups make models significantly more flexible

Usage and Style

There are four types of lookup formulas in Excel:
INDEX(MATCH)), LOOKUP(), VLOOKUP(), and HLOOKUP().

Syntax

Examples

ing the

break

mould

model

model

Lookup Formulas

Broad Strokes

Well implemented lookups make models significantly more flexible

Usage and Style

There are four types of lookup formulas in Excel: INDEX(MATCH)), LOOKUP(), VLOOKUP(), and HLOOKUP().

Syntax

Examples

There are four types of lookup formulas in Excel:
INDEX(MATCH)), LOOKUP(), VLOOKUP(), and HLOOKUP().