The Excel Table Lookup Functions – Part One

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.

1 Usage of LOOKUP in Excel

The widely used LOOKUP functions in Excel are VLOOKUP and HLOOKUP. VLOOKUP enables you to look through an information extend that is set up vertically. HLOOKUP is precisely the same capacity, however looks into information that has been designed by rows rather than column.

1.1 VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP have some downsides that could prompt potential blunders, particularly for people who are new to using a spreadsheet or financial model.

1.2 INDEX and MATCH

In specific cases, particularly when there is an unsorted range, a mix of INDEX and MATCH capacities could be a more accurate way of finding data than the LOOKUP capacities.

INDEX(MATCH) can be a more dominant solution in fiscal models than the old school VLOOKUP/HLOOKUP, and can be utilized to build the vigor of models in very developed and complex circumstances.

2 VLOOKUP, HLOOKUP in detail

2.1 VLOOKUP

VLOOKUP scans for values in the furthest left column of an information range, and after that returns a value in a same row from a row you selected in the range. VLOOKUP is utilized rather than HLOOKUP when your examination values are situated in a column to the left side of the information you are looking for.

The syntax for VLOOKUP will be VLOOKUP (lookup_value, table_array, column_index_num, range_lookup).

‘range_lookup’: If TRUE (or precluded), an estimated match is returned (i.e., if an exact match is not found, the next biggest value that is not as much as ‘lookup_value’ is returned). If FALSE, VLOOKUP will locate an exact match.

Prices of 2019 of the Golf range is shown in the image below as an example.

The first column is the model code, the second and third column represents the model description and fuel type. In the fourth and fifth columns prices are shown.

VLOOKUP equation is utilized for (a) solution as (it) appears in the image above. The “column_index_num” for prices 2019 inclusive charge would be “5”, for prices 2019, no charges mentioned, the number would be “6”. Remember that the “FALSE” is excluded from the “range_lookup” in this sample because the data in the first column are sorted in ascending order.

2.2 HLOOKUP

HLOOKUP functions the same way as VLOOKUP, however looks into data that has been organized by rows. HLOOKUP looks for a value in the top row of a table, and afterward returns a value in a similar column from a row you determine in the table.

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

 

2.3 ISSUES WITH VLOOKUP/HLOOKUP

Use caution if any rows or columns are added, moved or erased:

  • If any columns or rows are added, 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 example, if a section is added between column D and E in the image above, at that point you must make sure to change the ‘column_index_num’ for prices 2019 inclusive charge changes from ‘5’ to ‘6’.

 

VLOOKUP could look for a value in the first left column of data range. Thus, HLOOKUP could look for values in the top line of the range:

  • At the point when the key field is to right side of the range you need to recover, VLOOKUP will not work.

 

Value in the first row/column of range should be arranged if range_lookup is ‘TRUE’ (or excluded), except if the range_lookup is ‘FALSE’:

  • This could prompt potential blunders, particularly when the user is new to the function.

 

3 REPLACING VLOOKUP versus HLOOKUP with LOOKUP

The VLOOKUP/HLOOKUP are often used before a LOOKUP function. Nonetheless, the LOOKUP function is cleaner and very transparent, as it only needs two ranges and a LOOKUP value. The syntax for LOOKUP is LOOKUP (lookup_value, lookup_vector, result_vector).

 

4 THE USE OF INDEX(MATCH)

As discussed above, VLOOKUP and HLOOKUP functions have some downsides that could prompt potential blunders, particularly for users who are new to the model. In spite of the fact that the basic LOOKUP function could be used some of the time to replace VLOOKUP/HLOOKUP, it is constrained to a single row or column to hold results. In some situations, utilizing a mix of INDEX(MATCH) functions might be the only solution, because they are more powerful and adaptable than VLOOKUP/HLOOKUP.

INDEX returns the value dependent on the particular row and column number indexes: INDEX (array, row_num, column_num).

MATCH returns the position of the coordinated items in a one-dimensional list: (lookup_value, lookup_array, match_type) ‘match_type’: If match_type is 0, MATCH finds the first value that is actually equivalent to lookup_value.

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

  • Find the ‘row_num’ for selected model BQ13CX using the MATCH function – the solution is row # ‘6’
  • Find the ‘column_num’ for prices 2019 exclusive charge using the MATCH function – the solution is column # ‘4’
  • The sales forecast can then be solved using the INDEX function, using the ‘row_num’ and ‘column_num’ solved using the MATCH functions above