You have been tasked with creating a sales report or excel forms for your company. In this report, you need to include: the date of the sale, the customer’s ID number, the corresponding sales rep, and the amount of the sale. In order to compile this information, you need two reports:
A report that shows sales to customers. This will give you the date, customer ID, and the amount.
A list of customer IDs and their corresponding sales rep.
There are two ways you could merge this data together, assuming that you have both reports in excel spreadsheet format. The first method is as follows:
Step 1: Go to the first record in the sales sheet and look at the customer ID.
Step 2: Go to the customer list, find that same customer ID and take note of the sales rep.
Step 3: Go back to the sales sheet and enter the sales rep.
Step 4: Repeat steps 1-3 for each and every sales record.TIP: If you really want to do it this way (not recommended), then know that ALT+TAB will switch back and forth between windows quickly.
Now if you read the steps below, you are probably already feeling tired from all the back and forth. Don’t worry, this is normal.
However, there is an easier way. The second method makes use of our VLOOKUP function. Let’s see how:How To Use The VLOOKUP Function
Step 1: On the first record in your sales sheet, select the blank cell where you want the sales rep to be.
Step 2: Type the following formula:
=VLOOKUP(CustID,Range,Column#,FALSE)CustID = The cell in the first sales record which has the customer ID Range = The entire customer list (Note: the customer ID must be the first column)Column# = Counting from the left, the number of the column that contains the sales rep (i.e. – the first column selected in the range is 1, no matter what the letter is at the top)FALSE = The word “FALSE” tells your spreadsheet that the customer ID must be an exact match, rather than a partial match
Step 3: Copy the formula down to the last record in your sales sheet
TIP: You can drag the fill handle (the square in the bottom right corner of the selected cell) down to quickly copy the formula.
While there is much more involved for getting the sales rep for one record, think about how much time you will save if there are hundreds or even thousands of these records!
Well, that’s all there is to it. Don’t you feel proud of yourself? You should. You just learned one of the most useful formulas in Excel!
Great job and remember… Don’t just work, Excel!