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.
The VLOOKUP Forumula
The basic structure of a VLOOKUP is shown below:
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
As you can see this function has 4 arguments, the last one being optional as indicated by the [square brackets].
|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.
If a picture paints a thousand words…. what’s a gif worth?
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:
- 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...