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.

• 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.

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)

• 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.

Recommended Courses

Microsoft Excel is an integral part of the business landscape and it is a critical...