CODEWITHSUNDEEP
excelexcel-formula
erikburg
erikburg

Reputation: 3

Returning criteria matching results from a row into a single column

I have a table of data I want to be able to retrieve data from and put it into a column. Screenshot does a good job explaining what this looks like. So using criteria like "year" and "week" I want to return all the cell data that is not blank from the row that matches the year and week. Importantly I want to return it in a single column. So if there are 15 matching results across that particular week's row, I want a column that would list the 15 results.

example of data set

Upvotes: 0

Views: 59

Answers (2)

EDS
EDS

Reputation: 2195

You can use FILTER.

Something like:

=LET(x, FILTER($C$2:$G$7,(($A$2:$A$7=2021)*($B$2:$B$7=3)))
TRANSPOSE(FILTER(x, x<>""))

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

With Office 365 Excel use Nest two FILTER in TRANSPOSE:

=TRANSPOSE(FILTER(FILTER(C:G,(A:A=J2)*(B:B=J3)),FILTER(C:G,(A:A=J2)*(B:B=J3))>0))

enter image description here

Upvotes: 0

Related Questions