Use LOOKUP to replace VLOOKUP and HLOOKUP in Excel

A lookup function takes a value, finds it in a data range and returns related information from that range, without scrolling through a list. VLOOKUP and HLOOKUP are the best known, but they have drawbacks that lead to errors, especially for people who did not build the model. This tutorial shows when LOOKUP replaces them, and when INDEX and MATCH or XLOOKUP is the better choice.

VLOOKUP and HLOOKUP

VLOOKUP searches a range laid out in columns. HLOOKUP does the same for a range laid out in rows.

VLOOKUP

VLOOKUP looks for a value in the leftmost column of a range and returns the value in the same row from a column you choose. Use it when the values to compare sit in a column to the left of the data you want.

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

If range_lookup is TRUE or left out, VLOOKUP returns an approximate match: if there is no exact match, it returns the next largest value that is less than lookup_value. If range_lookup is FALSE, it looks for an exact match.

Figure 1 shows price data. The first column holds the price case, the next columns hold information about each case and the remaining columns hold the price for each calendar year.

To return the price for the selected price case, the col_index_num is 4 for CY2023, 5 for CY2024 and so on. range_lookup is FALSE because the first column is not sorted.

HLOOKUP

HLOOKUP does the same as VLOOKUP for data laid out in rows. It looks for a value in the top row of a range and returns the value in the same column from a row you choose.

HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Problems with VLOOKUP and HLOOKUP

• The column or row number is typed into the formula. If a column or row is inserted, moved or deleted inside the range, the formula does not adjust: insert a column in the middle of the price data in Figure 1 and every col_index_num after it must be changed by hand
• VLOOKUP can only search the leftmost column of a range, and HLOOKUP only the top row. If the value you search on sits to the right of the data you want, VLOOKUP cannot be used without moving columns
• With range_lookup TRUE or left out, the first column or row must be sorted. That is easy to miss, especially for someone who does not know the function well

Replacing VLOOKUP and HLOOKUP with LOOKUP

LOOKUP is cleaner and more transparent: it needs only a lookup value and two ranges.

LOOKUP(lookup_value, lookup_vector, result_vector)

LOOKUP is not suitable for the example in Figure 2. The lookup_vector is text and is not sorted from A to Z. When LOOKUP cannot find an exact match it returns the next smallest value, so here it returns the Base case instead of the case selected.

That way of matching is useful elsewhere, though. Where the lookup_vector is a number sorted in ascending order, such as the model timeline running left to right, it keeps the number of inputs down.

In Figure 3 the lookup_value is a calendar year counter that rises from the start to the end of the model timeline, so LOOKUP returns the right price from the Assumptions sheet.

Figure 4 shows what happens when the lookup_value is less than the smallest value in the lookup_vector. Here the lookup_value is the operations year counter, which is zero during construction. The generation per turbine table starts at operations year 1, so LOOKUP returns an error. An IF statement returns zero in those periods instead.

The workbook has further examples of LOOKUP in place of VLOOKUP and HLOOKUP.

INDEX and MATCH where LOOKUP falls short

LOOKUP can replace VLOOKUP and HLOOKUP in many cases, but it returns results from a single row or column. Where the range is not sorted, or the result sits in a two-dimensional table, INDEX and MATCH together are the safer and more flexible choice.

• INDEX returns the value at a given row and column number: INDEX(array, row_num, column_num)
• MATCH returns the position of an item in a one-dimensional list: MATCH(lookup_value, lookup_array, match_type). With match_type 0, MATCH finds the first value exactly equal to lookup_value

To solve the example with INDEX and MATCH:

• Use MATCH to find the row number of the Base case, which is 1
• Use INDEX with that row number to return the price

XLOOKUP in Excel 365

Excel 365 added several new functions, including XLOOKUP. It works much like LOOKUP, with three more inputs: if_not_found, match_mode and search_mode.

Entering zero in if_not_found removes the need for the IF statement in Figure 4, where the lookup_value is below the smallest value in the lookup_vector.

match_mode has four options, three of which are useful in financial modelling:

• 0: exact match
• -1: exact match or the next smallest value
• 1: exact match or the next largest value

So XLOOKUP with 0 does the job of INDEX and MATCH for an exact match, and with -1 it follows the logic of LOOKUP. It can also return the next largest value where there is no exact match.