*Don’t forget to subscribe below to get your free VLOOKUP Template…
The dreaded VLOOKUP continues to be the bain of our existence in our professional lives. However, it also remains one of the most desired Excel functions by businesses.
Nearly every job description for an analytical role mentions the function by name. Merely having Excel experience is not good enough, they need specifics.
The ability to perform VLOOKUPs effectively and correctly has been the gatekeeper to a number of advanced positions. I will teach you how to use VLOOKUPs so you can break down those barriers and have the job you deserve.
Before we can begin the process, you have to understand what we are trying to accomplish. In the simplest terms, VLOOKUP is a search and return function. You give it a value and ask it to find that same value within a given table.
Now, when it finds this value, it is going to confirm this by returning a value to you. It can return the original value you asked it to find or a different value from the table associated with that value.
Here is the structure of the formula:
The “lookup_value” is the original value that you are searching for. The “table_array” is the area that you are telling Excel to search in to find the lookup_value. One rule is that Excel will only look for the lookup_value within the first column of the table_array.
The “col_index_num” instructs the VLOOKUP which column within the table_array you want to return. For example, if your data was arranged like this: Col 1 = Account number, Col 2 = Date, Col 3 = Amount. If you use the col_index_num of 3, the vlookup formula will return values from the Amount column.
The “[range_lookup]” field is simple. It is either a “1” or a “0”. I have always used “0” because it instructs the formula to return an exact value. I’m sure there is a relevant situation where you may use a “1”, but I haven’t come across one yet.
Here is how the formula would be structured. =Vlookup(Target_Value, A1:C6, 1, 0).
Here is the source data.
This is the original list of values. Our formula is written on the same line as the first original value. We are telling the Vlookup formula to find the “33366773” account number within the Source data table. Notice that our Col_index_num is “1” so the return value will be from the Account Number field.
The finished formula searches for account number “33366773” and it finds it in the source table. We’ve instructed the formula to return the Account Number from the source data IF it finds a match, which it did.
In this example I used the formula: =VLOOKUP(A2,Source!A2:D38,2,0). Notice that I used a “2” for the Col_index_num”. If we refer to our Source table, you can see the second column is the Date field. That is why this formula returned the Date associated with the account number.
Error Checking – Example 3
In this example, there is an account number that is on the Original list, but is NOT in the Source table. The formula returns an #N/A when it cannot find the data.
One of the confusing things about using Vlookup is that it will return an #N/A if it cannot find the data AND if the formula is written incorrectly. So it is hard to know the reason behind the error. However, I have some tips to combat that uncertainty.
First, If only some of the values from your formula are #N/A’s then the formula is most likely correct. Second, if you get all #N/A’s then choose a random handful of the Original numbers and use the “Find” function (Crtl-f) within the source table. If the “Find” function does not find anything, your values probably do not exist in the Source table.
Another issue that may cause you trouble with your Vlookup has to do with formatting. If the format of the Account Number on the Original list is not the same as that field on the Source table, the formula may be recognizing the account number as a different value.
With most of us, we will be writing this formula on multiple lines. That means that we will need to be able to copy this formula without any errors. When we copy the formula down on the Original spreadsheet, we need to be comparing the entire Source table each time. So if you notice above, the formula for the source data is Source!A2:D38. If we begint he copy the Vlookup formula down to the next line, the formula will change to Source!A3:D39.
However, our Source Table data has not moved. The formula is just not capturing all of it anymore. We need to make the source table an absolute reference. That means placing “$” in front of the Column letter and the Row number. It would look like this: Source!$A$2:$D$38. As you copy the Vlookup down to different rows, the reference will remain the same.
I hope that after this post, you are no longer intimidated by the “Vlookup” formula. Almost every organization uses Excel and many expect you to be proficient. Being able to understand and execute a Vlookup may not only be a benefit, it may be a requirement. Do not miss out on an opportunity by being intimidated by something that you know you are capable of learning.
For more complete Excel training, I recommend Wall Street Prep’s guides and courses. They are by far the best on the market and will provide you will what you need to land the job of your dreams.
Happy (Job) Hunting!
Don’t forget to download the VLOOKUP template to help you write your first successful VLOOKUP. Subscribe below to get your free copy!