Excel vLookup Example
While teaching Excel classes over the past few semesters I get many questions on how to perform certain fuctions in Excel. Below is another request for and Excel vLookup example. This is for a student that needed this for real world work.
My student had two lists of customers. The first was a list of customers that did not pay bill No. 1. The second was a list that did not pay bill No. 2. She needed to combine the lists into a single list that included all of the data.
Download the example Excel file for reference: vlookup-example.zip (162 downloads)
Note that the below example is ficticious data and is simplified for learning. In reality these lists could contain hunders or thousdands of users.
The first worksheet contains a list of customers that did not pay Bill 1.
The second is a list of customers that did not pay Bill 2.
Notice there are some customers that have not paid either bill.
The first step is to get a list of unique customers in a column by itself to be used in the lookup. There are several ways to do this but for this example I am going to copy all of the user ids from Bill1 and paste them into a new worksheet in the same file. Then copy all of the user ids from Bill2 into the new worksheet as well.
This leaves us with all users in the same column including duplicates.
Next step is to sort and deduplicate the list. Excel has us covered with two buttons on the Data tab, “Sort” and “Remove Duplicates” as pictured below
After clicking the Remove Duplicates we are left with a list of unique users in both Bill 1 and Bill2 lists.
Now that we have the unique list we can add the Excel Vlookup Function. On the Formula tab click Insert Function then type vlookup to bring up the functon builder as shown below.
The Lookup_value is the column of unique users in our new workscheet. The Table_array is the data we will be looking at to pull the data we need. In this case it is the first two colums from the “Bill 1” worksheet. Col_index_num is the colum index number from the columns that we selected in the Table_array value. In this case we selected only two col and the data we would like to return is in the second colum. Range_lookup in my opinion should always be set to FALSE. This will ensure that we get an exact vaule rather than the closest option.
As we expect user one owes $100 on the Bill 1 sheet.
We can now copy the formula to cell C2 to lookup this user in the Bill2 sheet. Note we will need to adjust the formula as follows (change the name of the sheet Bill 1 to Bill 2) “=VLOOKUP(A:A,’Bill 2‘!A:B,2,FALSE)”.
Notice we are getting the error “#N/A”. This is because User 1 is not listed in the Bill 2 worksheet. We can make this a little prettier by wrapping our entire function in an “iferror()” function to replace the error with a 0. To do this we need to change the both cell B2 and C2 formulas to:
B2: “=IFERROR(VLOOKUP(A:A,’Bill 1′!A:B,2,FALSE),0)”
C2: “=IFERROR(VLOOKUP(A:A,’Bill 2′!A:B,2,FALSE),0)”
We now have changed the error code to “0”
At this point we can autofill our formulas in col B and C all the way down to lookup the vaules for the remaining users. We can also format the numbers to currency as well. The final result is below.
Hope this help some of my other readers and students. Please leave any questions or comments below.
To see more Excel Tips