The VLOOKUP is one of the most used formulas in Excel across all sorts of different industries. The “V” in VLOOKUP stands for vertical. It allows you to compare two lists. The formula looks for a value in the left most column of a worksheet of data and if it finds that value it returns the value from the same row in a column specified by you. This is a big one and an important formula so it gets its own tab on the companion Excel workbook.
Formula: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Our Data lists for the VLOOKUP example
Formula: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Let’s take a look at the anatomy of a VLOOKUP:
The “lookup_value” is the value you are looking for, to see if it is in both lists. The “table_array” is the table that you are going to have the formula look in. This is just a reference to the cells you want to look in. The “Col_Index_Num” is the column number you want the formula to return if it finds a match. The “Col_Index_Num” is the column you want to be returned if there is a match. It is relative to the first column which is 1. If you start in column A and you want to return whatever is in column C you would put a 3 in the column. The “Range_Lookup” is a “TRUE” or “FALSE” argument that tells Excel whether you want an exact match or a partial match. I’ve personally never used anything but an exact match, which is the “FALSE” argument.
The Example on the workbook
The example on the workbook compares two lists. One list has a list of trader names and the trader’s ID number. The other list has the stock name and the trader’s ID number. Our objective is to compare the first list to the second list and match the trader name to the stock on the second list. We do this using the formula: =VLOOKUP(F11, $A$10:$B$33, 2, FALSE).
Let’s break it down:
The “lookup_value” in our formula is F11 for the first row of the formula (Remember this is a relative reference because there are no $, so this cell reference will change when we drag the cell in down). Cell F11 is the ID number on the second list. The ID number is our unique identifier.
The unique identifier has to be the same on both lists that you are comparing. Numbers are usually best, but it can be text. This is what you use to compare the two lists. In our example we use the trader’s ID number as our unique identifier because it is on both lists and there is only one ID number for each trader on the list, making it unique.
The “table_array” is the area that you want Excel to look in to compare the two lists. In our example we we excel to look in the range “$A$10:$B$33”. It will look in the first column in the “table_array” for the “lookup_value”. The “col_index_number” is the column number you want returned, relative to the first column in the “table_array”. Let’s use our example to understand this. The first column in our “table_array” is column A, this is column number 1. When the VLOOKUP finds a match we want the data next to it to be the return value, so we put “2” as our “col_index_number” because we want the value in the 2nd column of our “table_array” returned whenever there is a match. The “range_lookup” value is the true or false argument saying whether you want the “lookup_value” to be an exact match or a partial match. We used “FALSE” because it has to be an exact match in order for it to be returned.
The “table_array” is the area that you want Excel to look in to compare the two lists. In our example we we excel to look in the range “$A$10:$B$33”. It will look in the first column in the “table_array” for the “lookup_value”. The “col_index_number” is the column number you want returned, relative to the first column in the “table_array”. Let’s use our example to understand this. The first column in our “table_array” is column A, this is column number 1. When the VLOOKUP finds a match we want the data next to it to be the return value, so we put “2” as our “col_index_number” because we want the value in the 2nd column of our “table_array” returned whenever there is a match. The “range_lookup” value is the true or false argument saying whether you want the “lookup_value” to be an exact match or a partial match. We used “FALSE” because it has to be an exact match in order for it to be returned.
In our example it is looking for the value of cell F11 in the range “$A$10:$A$33”. If it finds a match it will return what is next to that cell in the range “$B$10:$B$33”, because the “col_index_number” is 2. In our example cell F11’s value is 15. The VLOOKUP looks for 15 in the range “$A$10:$B$33” it finds a match in cell A25 and it returns what is in cell B25, the trader’s name: Mike. We then drag this formula down the entire list and we easily get the trader name matched to a the stock they are responsible for.
Think about the many ways this formula could be useful for what you do at work!
0 Comment to "The VLOOKUP Formula Explained! [ Download VLookup Examples ]"
Post a Comment