Is there a way to simulate the filter function in excel with a formula?

0
votes

Names down the rows. Dates across the columns. Each student has either a 1, "x" or no value for a given date.

I have a dropdown with a list of all the dates in my table (columns).

I want to create a formula that will search my table for the matching date and then look down that column for all the values of 1 and provide me with a list of names.

This is essentially an auto-populating, dynamic filter function.

I have thought about several formulas (Index Match, Index Match Match, Small Rows) and even pivot tables but no luck. The main issue seems to be, making the date (column address) dynamic in the formula.

=IFERROR(SMALL(IF((GANTT!$FT$14:$FT$1003=1),GANTT!$A$14:$A$1003,""),ROW()-16),"")

In the above formula, FT corresponds to the date Sunday, 7 Oct 2018 and produces all of the correct names for the static column of FT. How do I make the FT variable and link it to a dropdown? Can I use address and indirect in some way?

Could I use some kind of helper cells to tell the formula which column to look use, instead of search through years of data?

Sorry, I cant figure out how to post my sample data table.

NAME | DATE1 | DATE2 | DATE3
JON  |       |     1 |     4
Jane |       |     0 |     1
BILL |     1 |       |     2
1
excelexcel-formula
I'd say make your best attempt to add the sample data table to the question, even if the formatting is off, and someone can fix/edit it for you. – BigBen
Use the INDIRECT function to point to the column you want – Harassed Dad
Does the sample data work for basic illustration? – Forward Ed

1 Answers

0
votes

Assumptions - You data is laid out in the range B2:E5 - Target date choice cell H2 - Results in column J

Step 1 - Determine your target column

Going to use Match to determine which column you want

MATCH(H2,A2:E2,0)

Step 2 - From the column find the rows with 1 in them

Going to use the AGGREGATE function for this one. It will perform an array like calculation on all rows. you divide the row number by your search criteria. All false values will cause a divide by zero error and will get ignored leaving just a lift of what you are looking for.

AGGREGATE(14,6,row(B3:B5)/(index(B3:E5,0,MATCH(H2,A2:E2,0))=1),A1)

Step 3 - Pull the names from the corresponding rows

Use INDEX to reference your list of names feed it the row numbers you are interested in.

INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$5)/(INDEX($B$3:$E$5,0,MATCH($H$2,$B$2:$E$2,0))=1),ROW(A1)))

If you wrap that in an IFERROR function for when you run out of matching names you can display "" instead of an error message. You can place the following formula in J2 and copy down:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$5)/(INDEX($B$3:$E$5,0,MATCH($H$2,$B$2:$E$2,0))=1),ROW(A1))),"")

POC

Adjust ranges to suit your data. Make sure that if you are matching dates for column headers that the format of the date is the same of the search value in H2. ie if its a date stored as text they both have to be dates stored as text. If its an excel serial date integers then they both need to be serial date integers.