Vertical search across multiple tables.

At Tony’s request, in this tip, I will show you how we calculate the commission with the vertical search in multiple tables, depending on years of service in the company, which is selected from 2 tables.

In column B below, we have the names of the sellers, in column C, the service and in column D, the amount sold.

In the cells J3 to K28(1), we first create a table showing the data for the commission of employees with less than 5 years of service and in the cells M3 to N28(2), the commission of those with over 5 years of service.

We give the cell area J3 to K28, the name, Table1 and the cell area M3 to N28, the name, Table2 (to select specific cell areas see Course Excel 2007 Lesson 12).
To calculate the formula, we use the VLOOKUP and IF functions.

In cell E3, we type: = VLOOKUP (D3, IF (C3<5,Tabel1,Tabel2),2) and press Ctrl + Enter, which will keep the cell selected for further processing.

Click on the “Home” tab and select “Number” for “Percentage” (3) in the “Ribbon”.

Copy the formula down with the fill handle to cell E12 or double-click the bottom right corner of the cell to copy the formula down.

How does this formula work? The first column of a range of cells is searched vertically, after which the result is given a value from a cell in the same row of the range.

The first column in this example is determined by the IF function.

Thus, we find the value D3 in the table satisfies the value of the IF function (if the value of C3 is less than 5 search the value of D3 in Table 1 and indicate the of the result, otherwise, we search in the corresponding row in Table2).

In order to calculate the commission amount, we type this formula in cell F3: = D3* E3 and press Ctrl + Enter and copy the formula down.

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats