CODEWITHSUNDEEP
excelexcel-formula
Andreu Amoros
Andreu Amoros

Reputation: 103

Create a list from another, filtering values and in Excel for Microsoft 365

I have a column A of values that works as a filter (1 or 2) for the column B, which is the attribute. I need to create a new column (C), of values based on the second column but filtered with the column 1, as follows:

Example (values can be duplicates, as shown)

I tried the INDEX/MATCH formulas, without luck. I have not had any luck either with the Data/Filter menu options as I need column C to update automatically whenever a new value is entered into columns A and B.

Upvotes: 0

Views: 212

Answers (1)

Harun24hr
Harun24hr

Reputation: 36965

For Office365 use filter formula.

=FILTER(B2:B8,A2:A8=1,"")

enter image description here

Without Office365 use Index() and Aggregate()

=IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,ROW($A$2:$A$8)-ROW($A$1)/($A$2:$A$8=1),ROW(1:1))),"")

enter image description here

Upvotes: 1

Related Questions