SOLVED

# Filtering cells onto a new sheet based on values in a column

Copper Contributor

# 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
4 Replies
best response confirmed by cr9926 (Copper Contributor)
Solution

# Re: Filtering cells onto a new sheet based on values in a column

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".

# Re: Filtering cells onto a new sheet based on values in a column

Thank you! Adding this to my repertoire

# Re: Filtering cells onto a new sheet based on values in a column

@jmmcdanielAnyway to achieve this result through Excel 2019?  Im trying to perform something very similar.

# Re: Filtering cells onto a new sheet based on values in a column

1 best response

Accepted Solutions
best response confirmed by cr9926 (Copper Contributor)
Solution

# Re: Filtering cells onto a new sheet based on values in a column

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".