One of the most useful functions available in Excel is the Lookup functions. 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.

Efficent use of lookup functions in Excel

Screenshot 1: Example of VLOOKUP

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.

VLOOKUP and HLOOKUP

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

Index and Match

It will also be discussed in this Tutorial that in certain cases especially when there is an unsorted range, a combination of INDEX and MATCH functions could be a more robust solution to be used instead of the Lookup functions. We have also prepared a workbook to illustrate the concepts discussed in this Tutorial.

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.
In the following example, there is sales forecast data as presented in Screenshot 1. The first column in the data table is the Country, the next few columns are the various respective information and the remaining columns are the periodic sales forecast data for each country.

E.g. Retrieve the periodic sales forecast data for Country D!
VLOOKUP formula is used to solve as shown in the Screenshot. The “col_index_num” for sales forecast in period Mar-09 would be “5”, for period Jun-09 would be “6” and so on. Please note that the “FALSE” is omitted from the “range_lookup” in this example because the data in the first column is sorted (A to J).

HLOOKUP

HLOOKUP is the exact same function, 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)
The example in the workbook formats the sales forecast data in the VLOOKUP example by rows instead of columns and demonstrates how HLOOKUP function is used to look up the periodic sales forecast for the selected Country.

Problems with VLOOKUP / HLOOKUP

• Caution if any columns or rows are inserted / moved / deleted:
  The main drawback of these functions is if any columns or rows are inserted, moved or deleted within the range, the VLOOKUP & 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, and similarly HLOOKUP could only search for a value in the top row of the range:
  When let say the key field is to the right of the range you want to retrieve, VLOOKUP will not work. Following 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 temporary moved to the first column in the range
• Values in the first column / row of the data range needs 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 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)

Screenshot 2: Example of replacing VLOOKUP with LOOKUP

Please refer to the accompanied workbook on other examples to use 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 to users who often are unfamiliar to 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 combination of INDEX (MATCH) functions may be the only solution which is more robust / 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 sales forecast for Country B for period ending 30-Jun-09!

Screenshot 3: Example of INDEX (MATCH)

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

• Find the “row_num” for Country B using Match function. The solution is row # “3”.
• Find the “column_num” for Period ending Jun-09 using Match function. The solution is column # “2”.
• The sales forecast can then be solved using INDEX function, using the “row_num” and “column_num” solved using MATCH functions above.