Forum Discussion
Solved
Filtering cells onto a new sheet based on values in a column
I'm not sure how to use VLookup or Index to achieve this. I have a sheet that contains multiple columns of data. From that I want to create a second sheet that returns values from only two columns based on a third column. Eg. Sheet one has a list of customer data, on sheet two I would like it to only return values from columns C (Name) and E (Number) if column A contains "USA", without having blank rows where the Country column contains something other than USA.
| Sheet 1: | Sheet 2: | |||||||
| A | B | C | D | E | A | B | ||
| Country | Color | Name | Amount | Number | Name | Number | ||
| USA | Blue | Cats Inc. | 2 | 111222 | Cats Inc. | 111222 | ||
| Canada | Green | Dogs Ltd. | 5 | 444555 | Giraffe Corp | 335544 | ||
| UK | Red | Turtle Inc. | 3 | 666777 | Zebra Inc. | 775566 | ||
| UK | Yellow | Frog LLC | 7 | 888999 | Lions Ltd. | 994422 | ||
| Canada | Pink | Birds Corp | 1 | 221133 | ||||
| USA | Purple | Giraffe Corp | 9 | 335544 | ||||
| USA | Black | Zebra Inc. | 3 | 775566 | ||||
| USA | White | Lions Ltd. | 2 | 994422 | ||||
| Canada | Grey | Fish LLC | 1 | 441122 |
To achieve this in Excel, you can utilize the FILTER function if you're using a version of Excel that supports it (like Microsoft 365 or Excel 2019 and later). Here's how you can do it:
On Sheet2:
In cell A1, enter the formula:
=FILTER(Sheet1!C:C, Sheet1!A:A="USA")
In cell B1, enter the formula:
=FILTER(Sheet1!E:E, Sheet1!A:A="USA")
These formulas will populate columns A and B on Sheet2 with the names and numbers from Sheet1 where the country is "USA".
4 Replies
To achieve this in Excel, you can utilize the FILTER function if you're using a version of Excel that supports it (like Microsoft 365 or Excel 2019 and later). Here's how you can do it:
On Sheet2:
In cell A1, enter the formula:
=FILTER(Sheet1!C:C, Sheet1!A:A="USA")
In cell B1, enter the formula:
=FILTER(Sheet1!E:E, Sheet1!A:A="USA")
These formulas will populate columns A and B on Sheet2 with the names and numbers from Sheet1 where the country is "USA".jmmcdanielAnyway to achieve this result through Excel 2019? Im trying to perform something very similar.
- Thank you! Adding this to my repertoire
