Before we learn how to use Excel VLOOKUP function, we must learn WHEN do we use Excel VLOOKUP function?
Task: Imagine if you have to find when does train no. 6948 departs in the schedule chart above?
- Your approach: You will scan the first column top to bottom. When you find the same train no., you stop there. Then you scan that row rightwards. Once you reach to the fifth column, you see the answer 23:00.
- Observation: Please note that train no. 6948 is in the first column and it does not repeat. The train nos. are not sorted either.
Excel VLOOKUP function helps “pull” value from a table with the help of a matching ID or unique identifier.
In the example given below, we need to find asset description of three IDs in Table 2. Table 1 has the details of ID wise asset description.
You can already see that the ID 7951 refers to Computer Table. That’s because this is a simple example. However, using Excel VLOOKUP function, you can easily find asset descriptions of 1000s of such IDs in less than a minute. This function is very helpful when you have a large amount of data.
There are four pieces of information that you will need in order to write the Excel VLOOKUP function:
- lookup_value: Refer marker “A” in the picture above. First, parameter lookup_value is the unique value or ID Code whose Asset Description is to be found. g. 7951. Some refer such ID code as a primary key.
- table_array: Refer marker “B” in the picture above. It is the table whose first column must contain the lookup_value or primary key. One of the columns of the table_array will contain details of Asset Description.
- col_index_num: Refer marker “B” in the picture above. The table_array chosen above has 2 columns. The second column contains Asset Description. Hence, col_index_num is 2.
- range_lookup: 0 or FALSE indicates an exact match. It means if your primary key in the example above is 7951, then provide asset description only and only if the same key is found in the first column of table_array selected. Else #N/A! error will be given. We will discuss the situation when to use TRUE or 1 in another blogpost on Errors in Excel
Let’s walk through the process of writing Excel VLOOKUP function like a Pro.
Step 1: Refer VLOOKUP example picture given above. Type an = (equal sign) and begin typing “VL” to start an Excel VLOOKUP function. Excel displays a dynamic drop-down list of valid functions starting with the letters VL. Choose the desired function from such list and press TAB key. This will autocomplete the Excel VLOOKUP function =VLOOKUP(
Refer VLOOKUP example picture given above. Choose the table range where ID codes are given along with the asset description in subsequent columns. The first column of the table_array MUST contain the lookup_value or the primary key.
Refer VLOOKUP example picture given above. As soon as table_array is chosen, press function key F4 to lock the range selection with four $ signs. This ensures that as the function is copied and pasted below, the range remains B2:C6 for all. Not doing so may give errors instead of correct answers. Watch the VLOOKUP example picture below. The initial range B2:C6 has changed to B4:C8.
A reason for the error (#N/A): As the Excel VLOOKUP function is copied and pasted below, the table_array changes from B2:C6 to B4:C8. This means that the ID code 7082 is being looked up in the revised range. The revised range does not contain the ID code 7082. Avoiding mistakes in the formatting cells will help you use Vlookup more efficiently.
Step 5: Refer VLOOKUP example picture given above. Now we need to specify the correct column sequence number from the table array. Column number 2 refers to the second column of the chosen table_array. Col_index_num can be 2, 3, 4 … n. It cannot be a negative number or a fraction. E.g. using -1 will not give Serial No. 04.
Step 6: True (1) and False (0) refers to approximate and exact match, respectively. In the VLOOKUP example picture given above, we want the exact match. E.g. ID 7951 should give the answer “Computer Table”. If the ID 7951 were not present in Table 1, then #N/A will be the answer. It will not look for any close match such as ID 7950 or 7952.
Next Step: Simply drag the cell down using Autofill handle bar. This will help give answers for all the three ID codes.
Extra Caution: The cell F6 has been changed to ID code 7947. This ID code is not given in Table 1. Yet, it gives the answer “Computer Chair”. This is because the function is looking for an approximate match. If nothing is mentioned in the fourth parameter i.e. range_lookup takes the value TRUE (1) or an approximate match. This clearly is an incorrect answer. So this is how you can improve your productivity considerably with these MS Office Tips.