Making Excel Life Easier with the VLOOKUP Function
The VLOOKUP function can become very handy when you are trying to consolidate different reports into one easy to read spreadsheet. Let’s start with a look at our first sheet. This is where we will eventually pull all the data we need.
All of the data needed to fill these empty cells will be pulled from individual reports. Each of these reports will have their own sheet within the Excel book and can be found at the bottom of the page.
We’ll begin with the first empty cell, the contact person for each company. Reference the lower image for the Contact Info sheet.
The syntax of this function is VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).
- The lookup value will be the Company Name, as it is the common link between all the data we are filing in.
- The table array for the Contact Person is the ‘Contact Info’ sheet.
- The col_index_num (or column index number) value is the series of columns that hold the data you are looking for. In this case, we are looking at columns A-C, notated in the formula as A:C.
- Finally, the range lookup value is the numbered column within the specified index. Since we are looking for the Contact Person, the column number is two.
- You will end the function with a True or False statement. Using true will give an exact or approximate value; if the exact match is not found then Excel will fill in the next greatest value. Using false will give an exact match.
In this case our final formula will lead us to see that Company A has a contact person named Mark.
We can then use the Fill button, selecting the down option to fill the rest of the empty cells in this column.
You can repeat this process until all empty cells filled. You will adjust the formula to find the needed information for each column. For example, the contact phone number will use an identical formula to the the contact person listed above, however the column index number will be 3 instead of 2.
To fill in the Invoice Total column, your formula will again mimic the Contact Person formula.
Finally, repeat the formula again to fill in the description.
While this example is rather simplistic, this function is particularly useful when dealing with large amounts of data that are difficult to sort out by hand. Hopefully it will save you a great deal of time organizing the information you need.