Excel VLOOKUP Function

Let’s say we have the following data in Excel:

 

 

We have a list of invoices in columns A through D and a list of customers in columns F through H. We want to populate column B with the correct customer names from column H.

 

In this scenario, it might be easier to copy and paste the data from column G into column B for each customer number. However, this file could easily have 150,000 lines of invoices and 12,000 customers. This would be a very tedious task for a large file. Let’s take a look at how to populate column B using the VLOOKUP function.

 

We can put the following formula in cell B2 and paste it down through to cell B12:

=VLOOKUP(A2,$F$2:$H$12,3,FALSE)

 

The result is a correctly populated column B:

 

 

The VLOOKUP function takes four arguments:

·         Lookup_value – unique value to lookup

·         Table_array – range of data containing the lookup value in the far-left column

·         Col_index_num – the column number of the data the function should return

·         Range_lookup – TRUE to match the closest record; FALSE to only match if exact

 

Some notes about using VLOOKUP:

·         Table_array – If we copy and paste the formula “=VLOOKUP(A2,F2:H12,3,FALSE)” from cell B2 through B12, cell B2’s formula will read “=VLOOKUP(A2,F2:H12,3,FALSE)” while cell B12’s formula will read “=VLOOKUP(A12,F12:H22,3,FALSE)”. The problem is that, by pasting, Excel auto-incremented the Table_array value from “F2:H12” to “F12:H22”. In order to avoid this, it is better to use either the formula “=VLOOKUP(A2,F:H,3,FALSE)” or “=VLOOKUP(A2,$F$2:$H$12,3,FALSE)” ($ tells Excel not to auto-increment the values). That way, when we paste down column B, the Table_array value does not change.

·         Table_array – The first column in the Table_array value must be the value that is being lookup up. In our example, “Customer Number” is the value that we are looking up so “Customer Number” is in column F, the first column in our Table_array value.

·         Col_index_num – This is the number of the column that you want the data returned from. In the example, 1 is column F, 2 is column G, and 3 is column H. Since we wanted the customer name, I put 3 in the Col_index_num field.

·         Range_lookup – I have rarely encountered a scenario where I would want the Range_lookup value to be “TRUE”. Most often, I want an exact match. If I don’t find the customer number in my list of customers I don’t want any data returned. That is why I usually put “FALSE” in the Range_lookup field.

·         If the “Lookup_value” is not found in the “Table_array”, Excel returns the value “#N/A”. You can use the IF and ISNA functions to handle this scenario by putting the following formula in cell B2:

This tells Excel to return the value “CUSTOMER NOT FOUND” if VLOOKUP returns “#N/A”, otherwise, use the value returned by VLOOKUP.

The result looks like this in our example if we remove the customer number from cell F6: