1800-222-WTF

Excel & VBA

26 Feb 2018

Unleash the Power of VLOOKUP

/
Posted By
/
Comments0

At a Glance

VLOOKUP is an Excel function we can use to retrieve data from other locations. So if you have a table elsewhere on your sheet, this is the function to use to find specific data you want to use from it.

Find you with VLOOKUP

Figure 1: I will find you… with VLOOKUP

The VLOOKUP Forumula

The basic structure of a VLOOKUP is shown below:

As you can see this function has 4 arguments, the last one being optional as indicated by the [square brackets].

Argument Description
lookup_value The value you wish to ‘lookup’. It must be in the first column of the data set you select (see table_array). Therefore, if your data range is A1:D50, the lookup value will be searched for in column A.
table_array This is the table you wish to search using the lookup_value and then return data from.
col_index_num The column number that contains the data you seek, once a match is found for the lookup_value. The left-most column is number 1, and increases sequentially as you move right.
range_lookup [Optional] A logical value that specifies if you want an exact or approximate match.

TRUE – This is the default condition if none is specified. Assumes the first column in the table is sorted alphanumerically, and will then search for the closest value.

FALSE – Searches for an exact match.

Worked Example

If a picture paints a thousand words…. what’s a gif worth?

VLOOKUP Example

Figure 2: VLOOKUP Example

So in the example above, we want to LOOKUP the Description & Price of an item just by using the Part ID. Pretty simple right? Can you guess the VLOOKUP formula that I used to find out the price? Have a go, and check if you got it right in the hidden code below:

Test Yourself: VLOOKUP - Price

=VLOOKUP(C11,C3:D7,3,FALSE)

Additional Points

  • VLOOKUP can only look for data on the right hand side of the search column (where the lookup_value resides)
  • It retrieves data based on the column number you specify (relative from the start column)
  • Only works when records of data are arranged vertically (one above the other). For horizontally arranged data we can use the HLOOKUP (TBC in another lesson)

 

I correct autocorrect... more than autocorrect corrects me...

Leave a Reply