Use LOOKUP to replace VLOOKUP and HLOOKUP functions in Excel

One of the most useful functions available in Excel is the LOOKUP function. This allows you to take any value entered, find it in a data range, then return a value or information from that same data range without having to scroll through a list.

Efficient use of LOOKUP functions in Excel

LOOKUP

The most commonly used LOOKUP functions in Excel are VLOOKUP and HLOOKUP. VLOOKUP allows you to search a data range that is set up vertically. HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns.

LOOKUP and related functions are commonly used for business analytics in Excel as a way of slicing and dicing data for analysis. We also cover this topic in our Excel training courses.

VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP functions have certain drawbacks that could lead to potential errors, especially for users who are often unfamiliar with the spreadsheet or financial model. These issues will be discussed in this tutorial, together with a simple LOOKUP function which could be used to replace the VLOOKUP/HLOOKUP in the model.

INDEX and MATCH

In certain cases, especially when there is an unsorted range, a combination of INDEX and MATCH functions could be a more robust solution than the LOOKUP functions. Our downloadable workbook illustrates these concepts – see links at the top or bottom of this page.

INDEX(MATCH) can be a more powerful solution in financial models than the traditional VLOOKUP/HLOOKUP, and can be used to increase the robustness of models in more advanced situations, such as our LBO financial modelling courses and advanced project finance modelling courses.

Detailed examples of VLOOKUP, HLOOKUP, LOOKUP and INDEX(MATCH)

VLOOKUP

VLOOKUP searches for a value in the leftmost column of a data range, and then returns a value in the same row from a column you specify in the range. VLOOKUP is used instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The syntax for VLOOKUP is VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

‘range_lookup’: If TRUE (or omitted), an approximate match is returned (i.e., if an exact match is not found, the next largest value that is less than ‘lookup_value’ is returned). If FALSE, VLOOKUP will find an exact match.

As an example, price data is shown in screenshot 1. The first column in the data table is the price case; the next few columns includes respective information; and the remaining columns are for the periodic price data for each calendar year.

EXAMPLE: Retrieve the price data for the selected price case.

VLOOKUP formula is used to solve as shown in the screenshot. The “col_index_num” for the price in period CY2023 would be “4”, for period CY2024 would be “5” and so on. Please note that the “FALSE” is included in the “range_lookup” in this example because the data in the first column is unsorted.

HLOOKUP

HLOOKUP performs the same function as VLOOKUP, but looks up data that has been formatted by rows. HLOOKUP searches for a value in the top row of a table (or an array of values), and then returns a value in the same column from a row you specify in the table or array.

The syntax for HLOOKUP is HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)

Problems with VLOOKUP/HLOOKUP

Use caution if any columns or rows are inserted, moved or deleted:

• If any columns or rows are inserted, moved or deleted within the range, the VLOOKUP and HLOOKUP will return the position of the new column or row in the lookup column/row index number.
• For instance, if a column is inserted between column D and E in screenshot 1, then the user must remember to change the ‘col_index_num’ for sales forecast in period Mar-09 from ‘5’ to ‘6’

VLOOKUP could only search for a value in the first leftmost column of a data range. Similarly, HLOOKUP could only search for a value in the top row of the range:

• When the key field is to the right of the range you want to retrieve, VLOOKUP will not work.
• Using the above example, if you want to retrieve the major client and the country for Priority 1 sales, then VLOOKUP can’t be used, unless the priority column is temporarily moved to the first column in the range.

Values in the first column/row of the data range need to be sorted if range_lookup is ‘TRUE’ (or omitted), unless the range_lookup is ‘FALSE’:

• This could lead to potential errors, especially when the user is not familiar with the function

How to replace VLOOKUP/HLOOKUP with LOOKUP

The VLOOKUP/HLOOKUP functions are often used before a LOOKUP function. However, the LOOKUP function is far cleaner and more transparent, as it only requires two ranges and a LOOKUP value. The syntax for LOOKUP is LOOKUP (lookup_value, lookup_vector, result_vector).

However for the example in screenshot 2, you can see quite clearly that the LOOKUP function will not be appropriate as the lookup_vector is text and not sorted in ascending (A to Z) order. As LOOKUP will return the next smallest match where a result cannot be found, it returns the “Base case”: L comes after H in the alphabet, so B is the next smallest result in this instance.

Although for the example in screenshot 2 this is not satisfactory, the method of matching is advantageous in certain situations. If the lookup_vector is a number which is sorted in ascending order (e.g., the model timeline which progresses left-to-right), we are able to use this to minimise the number of inputs required.

As you can see in screenshot 3, the lookup_value is our calendar year counter, an ascending counter from the start to the end of the model timeline. Therefore, LOOKUP is an effective method to return the corresponding price from the Assumptions worksheet.

However, screenshot 4 shows the impact of where the lookup_value is less than the minimum value in the lookup_vector. In this example, our lookup_value is the operations year counter, which has a zero value during the construction phase. If we try to lookup zero in the generation per turbine input table, which has a minimum value of 1 (i.e., OY 1 is the first year of operations), LOOKUP returns an error. Therefore we will need to isolate this error and use an IF statement to return zero where this is the case, as illustrated.

Please refer to the accompanying workbook on other examples of using the simple LOOKUP function instead of VLOOKUP/HLOOKUP.

The use of INDEX(MATCH)

As described above, VLOOKUP and HLOOKUP functions have certain drawbacks that could lead to potential errors, especially for users unfamiliar with the model. Although the simple LOOKUP function could sometimes be used to replace VLOOKUP/HLOOKUP, it is limited to a single row or column to hold results. In some cases, using a combination of INDEX(MATCH) functions may be the only solution, as they are more robust and versatile than VLOOKUP/HLOOKUP.

• INDEX returns the value based on the specific row and column number indexes: INDEX (array, row_num, column_num)
• MATCH returns the position of the matched item in a one-dimensional list: MATCH (lookup_value, lookup_array, match_type) ‘match_type’: If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value (e.g., retrieve the price case for the period CY2023)

The steps to solve the example using INDEX(MATCH) are:

• Find the ‘row_num’ for the “Base case” using the MATCH function – the solution is row # ‘1’
• The sales forecast can then be solved using the INDEX function, using the ‘row_num’ solved using the MATCH functions above

An introduction to XLOOKUP

With Excel 365, Microsoft have introduced a variety of new and improved functions including XLOOKUP. It is mostly the same as LOOKUP, however with three additional inputs: if_not_found, match_mode, search_mode.

We are able to use the if_not_found field to negate the need for an IF statement where the lookup_value is less than the minimum value in the lookup_vector of a LOOKUP function, simply by entering a zero.

The match_mode has four choices, three of which we find useful in financial modelling:

• 0 – exact match
• -1 – exact match or next smallest value
• 1 – exact match or next largest value

This means we can use XLOOKUP to perform the same role as INDEX(MATCH) for an exact match by using a “0” input in the match_mode. Alternatively, it will replicate the logic of LOOKUP where we use “-1”. We also have added functionality with the addition of being able to match the next largest value where no exact match can be found.