How to Pull Data from Another Sheet Based on Criteria in Excel
To pull data from another sheet based on criteria in Excel, you can use various formulas such as VLOOKUP, INDEX and MATCH, or the newer XLOOKUP function (available in Excel 365 and Excel 2019 onwards). In this guide, we will use the INDEX and MATCH combination, as it is a versatile and powerful way to pull data from another sheet.
Here are the steps to pull data from another sheet based on criteria in Excel:
- Organize your data in the source sheet and the sheet where you want to pull data.
- Use the INDEX and MATCH functions in combination to look up the data based on specific criteria.
Let's see an example to understand the process.
Example
Suppose you have a source sheet named "SalesData" with the following data:
| OrderID | Product | Salesperson | Revenue |
|---|---|---|---|
| 1 | A | John | 1000 |
| 2 | B | Jane | 2000 |
| 3 | A | John | 1500 |
| 4 | C | Jane | 3000 |
You want to pull the Revenue data for a specific product and salesperson combination in another sheet named "Summary". The "Summary" sheet looks like this:
| Product | Salesperson | Revenue |
|---|---|---|
| A | John | |
| B | Jane |
To pull the Revenue data based on the Product and Salesperson criteria, follow these steps:
- In the "Summary" sheet, select the cell where you want to display the Revenue (e.g., C2).
- Enter the following formula:
=INDEX(SalesData!$D$2:$D$5, MATCH(1, (SalesData!$B$2:$B$5=A2)*(SalesData!$C$2:$C$5=B2), 0))- Press
Ctrl+Shift+Enterto enter the formula as an array formula. Excel will automatically wrap the formula in curly braces{}to indicate it's an array formula. - Drag the formula down to fill the other cells in the Revenue column.
The Revenue data should now be correctly pulled based on the Product and Salesperson criteria.
In this example, the INDEX function retrieves the Revenue data from the "SalesData" sheet, while the MATCH function finds the row number with the specified criteria (Product and Salesperson). By using these functions in combination, you can efficiently pull data from another sheet based on criteria in Excel.