How to use MS Excel's VLOOKUP function

aat comment

In his third post guiding you through useful Microsoft Excel tools for accountants, AAT student Muhammad Anisur Rahman shows you how to use the VLOOKUP function

The Vertical Lookup (VLOOKUP) function is used to search the first column of a range of cells or a table, and return a value from any cell on the same row of the range or table.

For this blog example, create a table as shown below:

Step 1: create this table

VLOOKUP Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Arguments

lookup_value (required)
The value to search in the first column of the table or range. The lookup_value argument can be a value or a Cell reference.

In this example, our first look up value is ‘Hove’ (Cell reference A9)

table_array (required)
The range of cells or data table that contains the data. In this example, select range A3:C15

col_index_num (required)
The column number in the table_array (Cell reference A3:C15) argument from which the matching value must be returned.

A col_index_num argument of 1 returns the value in the first column ‘Area Manager’ in table_array.

A col_index_num of 2 returns the value in the second column ‘Total Sales’ in table_array, and so on.

range_lookup (optional)

A logical (True or False) value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

For True or omitted:
The values in the first column (in this example column C ‘Area Manager’) of table_array must be placed in ascending sort (A to Z) order; otherwise, VLOOKUP might not return the correct value.

For False:
VLOOKUP will find only an exact match. If there are two or more values (duplicate ‘Area’ names) in the first column A (‘Area’) of table_array that match the lookup_value, the first value (‘Area’ name) found is used. If an exact match is not found, this will return the error value #N/A.
Create a another table as per below to show our lookup results

Create a another table as per below to show our lookup results:

Step 2: create another table

In Cell F3 type formula =VLOOKUP(E3,A3:C15,2,FALSE) to lookup ‘Area Manager’

  • Cell E3 is lookup_value reference
  • Cell reference A3:C15 is table_array reference
  • col_index_num 2 to find ‘Area Manager
  • FALSE for exact match

Also, in Cell G3 type =VLOOKUP(E3,A3:C15,3,FALSE) to lookup ‘Total Sales’

Type ‘Hove’ in Cell E3 as per image below:

Step 3: type 'Hove' in Cell E3

Now, type other area names from column A to lookup ‘Area Manager’ and ‘Total Sales’. If an area name entered in Cell E3 (for example ‘Paris’) is not found in our table, an error value #NA is returned.

Step 4

 

If ‘Area’ name in Cell E3 entered correctly and exists in our table but the lookup value is returned #NA, make sure that the data in the first column (range A1:A15, column title ‘Area’) of table_array does not contain leading spaces, trailing spaces, inconsistent use of straight ( ‘ or ” ) and curly ( ‘ or “) quotation marks, or non-printing characters.

In the next part of my blog I will demonstrate the SUMIF function. If you have any suggestions or question regarding this example, please feel free to leave your comment or contact me directly.

AAT members have access to an online training resource covering the key Excel functions critical to everyday business.

Muhammad Anisur Rahman has qualified with AAT.

Related articles