1800-222-WTF

Excel & VBA

26 Feb 2018

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. 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? 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)