CODEWITHSUNDEEP
excelexcel-formuladashboard
vincent.27
vincent.27

Reputation: 25

INDEX MATCH to return multiple results based on date range and name criteria

I'm trying to create a dashboard to return multiple results based on a name and date range criteria. The date range is Start date to End date. I tried using INDEX/MATCH code as below:

=INDEX($A$3:$A$12,MATCH(1,(($C$3:$C$12=$H$2)*($B$3:$B$12>=$H$3)*($B$3:$B$12<=$H$4)),0))

but I was only able to display the results for a set of data. I want to display more than 1 result for the specific name and date range. For example as in the picture, the result for Client name Albert should show 2 results for the specific date range and name, but I was only able to return 1 result. I want to populate the cells with all the results for that specific date range and name. How do I do this?

My working excel file. enter image description here

Upvotes: 1

Views: 1913

Answers (1)

Harun24hr
Harun24hr

Reputation: 37145

If you have Microsoft-365 then use FILTER() function like-

=FILTER(A3:D10,(C3:C10=H2)*(B3:B10>=H3)*(B3:B10<=H4),"No Record Found")

For older version of excel you can use AGGREGATE() function. See second screenshot.

=IFERROR(INDEX($A$3:$D$10,AGGREGATE(15,6,(ROW($3:$10)-ROW($2:$2))/(($C$3:$C$10=$H$2)*($B$3:$B$10>=$H$3)*($B$3:$B$10<=$H$4)),ROW(1:1)),COLUMN(A$1)),"")

Screenshot of FILTER function. enter image description here

Screenshot of Aggregate function. enter image description here

Upvotes: 1

Related Questions