Sometimes, you may need to look up a value from one table based on a criteria from another table. For example, you may have a table of products and a table of sales, and you want to find the total sales of a specific product. How can you do this in Excel?
There are different ways to look up values from multiple tables in Excel, depending on the structure and layout of your data. In this article, we will explore some of the most common methods, such as:
- Using VLOOKUP with multiple lookup tables
- Using INDEX and MATCH with multiple criteria
- Using SUMIFS with multiple criteria ranges
We will also explain the basic theory behind each method, and provide a step-by-step guide on how to use them. We will also show you a practical example with real data, and compare the advantages and disadvantages of each method.
Using VLOOKUP with multiple lookup tables
One of the most popular functions in Excel is VLOOKUP, which allows you to look up a value in a column based on a matching value in another column. However, VLOOKUP has some limitations, such as:
- It can only look up values from left to right, meaning that the lookup column must be the first column in the table array.
- It can only handle one lookup value and one lookup table at a time, meaning that you cannot use it to look up values from multiple tables or based on multiple criteria.
To overcome these limitations, you can use VLOOKUP with multiple lookup tables by using the IF function inside the VLOOKUP function. The IF function can test a condition and return different values based on whether the condition is true or false. By using the IF function, you can specify which lookup table to use based on a certain condition.
For example, suppose you have two lookup tables, one for the commission rate of salespeople who have been with the company for less than 2 years, and another for those who have been with the company for more than 2 years. You also have a table of sales data, and you want to calculate the commission for each salesperson based on their sales amount and their years of service. You can use the following formula to do this:
=VLOOKUP(D2,IF(C2<2,table1,table2),2,0)*E2
Where D2 is the sales amount, C2 is the years of service, table1 and table2 are the names of the lookup tables, and E2 is the commission amount.
This formula works as follows:
- The IF function tests whether the value in C2 is less than 2, which means the salesperson has been with the company for less than 2 years.
- If the condition is true, the IF function returns table1 as the table array for the VLOOKUP function. If the condition is false, the IF function returns table2 as the table array for the VLOOKUP function.
- The VLOOKUP function then looks up the value in D2, which is the sales amount, in the first column of the table array returned by the IF function, and returns the corresponding value from the second column, which is the commission rate.
- The formula then multiplies the commission rate by the sales amount to get the commission amount.
Here is an example of how the formula works with some sample data:
| Salesperson | Years | Sales | Commission Rate | Commission |
|---|---|---|---|---|
| Alice | 1 | 10000 | 5% | 500 |
| Bob | 3 | 15000 | 10% | 1500 |
| Charlie | 2 | 20000 | 7% | 1400 |
| David | 4 | 25000 | 12% | 3000 |
The advantage of using VLOOKUP with multiple lookup tables is that it is simple and easy to use, as long as you have a clear condition to determine which lookup table to use. However, the disadvantage is that it can only handle one condition at a time, and it still requires the lookup column to be the first column in the table array. If you have more complex or multiple conditions, or if your lookup column is not the first column in the table array, you may need to use a different method.
Using INDEX and MATCH with multiple criteria
Another way to look up values from multiple tables in Excel is to use the combination of INDEX and MATCH functions. The INDEX function can return a value from a specific position in a range or array, while the MATCH function can return the relative position of a value in a range or array. By using these two functions together, you can look up a value from any column in a table array based on a matching value from any column in the same or another table array.
For example, suppose you have a table of products and a table of sales, and you want to find the total sales of a specific product. You can use the following formula to do this:
=INDEX(sales[Total],MATCH(products[Product],sales[Product],0))
Where sales[Total] is the column of total sales in the sales table, products[Product] is the column of product names in the products table, and sales[Product] is the column of product names in the sales table.
This formula works as follows:
- The MATCH function looks up the value in products[Product], which is the product name, in the sales[Product] column, and returns the relative position of the matching value. For example, if the product name is “A”, the MATCH function returns 1, because “A” is the first value in the sales[Product] column.
- The INDEX function then returns the value from the sales[Total] column at the position returned by the MATCH function. For example, if the MATCH function returns 1, the INDEX function returns the value from the sales[Total] column in the first row, which is the total sales of product “A”.
Here is an example of how the formula works with some sample data:
| Product | Price | Quantity |
|---|---|---|
| A | 10 | 100 |
| B | 20 | 50 |
| C | 30 | 40 |
| D | 40 | 30 |
| Product | Total |
|---|---|
| A | 1000 |
| B | 1000 |
| C | 1200 |
| D | 1200 |
The advantage of using INDEX and MATCH with multiple criteria is that it is more flexible and powerful than VLOOKUP, as it can look up values from any column in any table array, and it can handle multiple criteria by using arrays or other functions. However, the disadvantage is that it is more complex and difficult to use, as it requires more arguments and nested functions, and it may require entering the formula as an array formula with Ctrl + Shift + Enter.
Using SUMIFS with multiple criteria ranges
Another way to look up values from multiple tables in Excel is to use the SUMIFS function. The SUMIFS function can sum the values in a range that meet multiple criteria in multiple ranges. By using this function, you can look up a value from one table based on a criteria from another table, and sum the values that match the criteria.
For example, suppose you have a table of products and a table of sales, and you want to find the total sales of a specific product. You can use the following formula to do this:
=SUMIFS(sales[Total],sales[Product],products[Product])
Where sales[Total] is the column of total sales in the sales table, sales[Product] is the column of product names in the sales table, and products[Product] is the column of product names in the products table.
This formula works as follows:
- The SUMIFS function sums the values in the sales[Total] column that meet the criteria specified by the other arguments.
- The first criteria range is the sales[Product] column, and the first criteria is the value in the products[Product] column, which is the product name. The SUMIFS function only sums the values in the sales[Total] column that have the same product name as the value in the products[Product] column.
- The formula can have more criteria ranges and criteria if needed, but in this case, there is only one.
Here is an example of how the formula works with some sample data:
| Product | Price | Quantity |
|---|---|---|
| A | 10 | 100 |
| B | 20 | 50 |
| C | 30 | 40 |
| D | 40 | 30 |
| Product | Total |
|---|---|
| A | 1000 |
| B | 1000 |
| C | 1200 |
| D | 1200 |
The advantage of using SUMIFS with multiple criteria ranges is that it is simple and easy to use, as it only requires one function and a few arguments, and it can handle multiple criteria in multiple ranges. However, the disadvantage is that it can only return a sum of values, not a single value, and it may not work well with non-numeric values or complex criteria.
Practical Example
To illustrate how to use these methods in practice, let’s look at a real-world example. Suppose you have the following data in Excel:
| Employee | Department | Group | Salary |
|---|---|---|---|
| Alice | Sales | A | 5000 |
| Bob | Marketing | B | 4000 |
| Charlie | Finance | C | 6000 |
| David | Sales | A | 7000 |
| Eve | Marketing | B | 3000 |
| Frank | Finance | C | 8000 |
You also have two lookup tables, one for the bonus rate of each group, and another for the tax rate of each department. The lookup tables look like this:
| Group | Bonus Rate |
|---|---|
| A | 10% |
| B | 15% |
| C | 20% |
| Department | Tax Rate |
|---|---|
| Sales | 25% |
| Marketing | 30% |
| Finance | 35% |
You want to calculate the net income of each employee, which is the salary plus the bonus minus the tax. How can you do this using the methods we discussed earlier?
Using VLOOKUP with multiple lookup tables
To use VLOOKUP with multiple lookup tables, you need to use the IF function to determine which lookup table to use based on a condition. In this case, the condition is the group of the employee, and the lookup tables are the bonus rate table and the tax rate table. You can use the following formula to calculate the net income of each employee:
=Salary+VLOOKUP(Group,bonus,2,0)*Salary-VLOOKUP(Department,tax,2,0)*Salary
Where Salary is the salary of the employee, Group is the group of the employee, bonus is the name of the bonus rate table, Department is the department of the employee, and tax is the name of the tax rate table.
This formula works as follows:
- The formula first adds the salary of the employee to the product of the bonus rate and the salary, which gives the gross income of the employee.
- The formula then subtracts the product of the tax rate and the salary, which gives the tax amount of the employee.
- The formula then returns the difference between the gross income and the tax amount, which is the net income of the employee.
Here is an example of how the formula works with some sample data:
| Employee | Department | Group | Salary | Net Income |
|---|---|---|---|---|
| Alice | Sales | A | 5000 | 3125 |
| Bob | Marketing | B | 4000 | 2280 |
| Charlie | Finance | C | 6000 | 3120 |
| David | Sales | A | 7000 | 4375 |
| Eve | Marketing | B | 3000 | 1710 |
| Frank | Finance | C | 8000 | 4160 |
Using INDEX and MATCH with multiple criteria
To use INDEX and MATCH with multiple criteria, you need to use arrays or other functions to combine the criteria and the criteria ranges. In this case, the criteria are the group and the department of the employee, and the criteria ranges are the bonus rate table and the tax rate table. You can use the following formula to calculate the net income of each employee:
=Salary+INDEX(bonus[Rate],MATCH(Group,bonus[Group],0))*Salary-INDEX(tax[Rate],MATCH(Department,tax[Department],0))*Salary
Where Salary is the salary of the employee, Group is the group of the employee, bonus[Rate] and bonus[Group] are the columns of the bonus rate and the group in the bonus rate table, Department is the department of the employee, and tax[Rate] and tax[Department] are the columns of the tax rate and the department in the tax rate table.
This formula works as follows:
- The formula first adds the salary of the employee to the product of the bonus rate and the salary, which gives the gross income of the employee. The bonus rate is returned by the INDEX function, which uses the MATCH function to find the position of the matching group in the bonus rate table.
- The formula then subtracts the product of the tax rate and the salary, which gives the tax amount of the employee. The tax rate is returned by the INDEX function, which uses the MATCH function to find the position of the matching department in the tax rate table.
- The formula then returns the difference between the gross income and the tax amount, which is the net income of the employee.
Here is an example of how the formula works with some sample data:
| Employee | Department | Group | Salary | Net Income |
|---|---|---|---|---|
| Alice | Sales | A | 5000 | 3125 |
| Bob | Marketing | B | 4000 | 2280 |
| Charlie | Finance | C | 6000 | 3120 |
| David | Sales | A | 7000 | 4375 |
| Eve | Marketing | B | 3000 | 1710 |
| Frank | Finance | C | 8000 | 4160 |
Using SUMIFS with multiple criteria ranges
To use SUMIFS with multiple criteria ranges, you need to specify the sum range and the criteria ranges and criteria. In this case, the sum range is the salary of the employee, and the criteria ranges and criteria are the group and the department of the employee, and the bonus rate and the tax rate tables. You can use the following formula to calculate the net income of each employee:
=Salary+SUMIFS(bonus[Rate],bonus[Group],Group)*Salary-SUMIFS(tax[Rate],tax[Department],Department)*Salary
Where Salary is the salary of the employee, Group is the group of the employee, bonus[Rate] and bonus[Group] are the columns of the bonus rate and the group in the bonus rate table, Department is the department of the employee, and tax[Rate] and tax[Department] are the columns of the tax rate and the department in the tax rate table.
This formula works as follows:
- The formula first adds the salary of the employee to the product of the bonus rate and the salary, which gives the gross income of the employee. The bonus rate is returned by the SUMIFS function, which sums the values in the bonus rate column that match the group of the employee.
- The formula then subtracts the product of the tax rate and the salary, which gives the tax amount of the employee. The tax rate is returned by the SUMIFS function, which sums the values in the tax rate column that match the department of the employee.
- The formula then returns the difference between the gross income and the tax amount, which is the net income of the employee.
Here is an example of how the formula works with some sample data:
| Employee | Department | Group | Salary | Net Income |
|---|---|---|---|---|
| Alice | Sales | A | 5000 | 3125 |
| Bob | Marketing | B | 4000 | 2280 |
| Charlie | Finance | C | 6000 | 3120 |
| David | Sales | A | 7000 | 4375 |
| Eve | Marketing | B | 3000 | 1710 |
| Frank | Finance | C | 8000 | 4160 |
Conclusion
In this article, we have learned how to look up values from multiple tables in Excel using different methods, such as:
- Using VLOOKUP with multiple lookup tables
- Using INDEX and MATCH with multiple criteria
- Using SUMIFS with multiple criteria ranges
We have also explained the basic theory behind each method, and provided a step-by-step guide on how to use them. We have also shown you a practical example with real data, and compared the advantages and disadvantages of each method.
