Index&Match inc date range query

[katemc]

Hello, I am new to excel and functions/formulas so any help would be appreciated.

I have a worksheet with a lot of data on, I am wanting a summary overview of the data based on a few factors. The area I am stuck is showing all values based on multiple critieria including a date range; for example – column X shows all ‘reasons’ and if columns a,b & c match 1,2,3 then show all values in column X in my new worksheet.

I have been working with Index&Match however that only returns 1 value, whereas I want it to return all. I also need to filter to the previous month and I’m not sure how to include this in my function. I have been working on the below function and it’s returning 1 value but not everything I want it to:

=INDEX(column X:X,MATCH(B66, column A:A,0)*(column B:B=B50)*(column C:C=C60) & (COUNTIFS(Table_owssvr[Check Complete Date],">="&H4, Table_owssvr[Check Complete Date], "<="&EOMONTH(H4,0))))

H4 cell reference shows ‘October 2021’ as my date for the overview.

I’m not sure if what I want to do is possible within the formula or if I should look at VBA or even other types of coding.

Hopefully this makes sense.

Thanks

 

[Alex Blakenburg]

Since you have 365 you can use the filter function for this. You can see how to do it here:-
Excel FILTER function with formula examples

The syntax is:
FILTER(array, (range1=criteria1) * (range2=criteria2), "No results")

 

[katemc]

Since you have 365 you can use the filter function for this. You can see how to do it here:-
Excel FILTER function with formula examples

The syntax is:
FILTER(array, (range1=criteria1) * (range2=criteria2), "No results")

Apologies-it’s version 2016 I am using. Any other solutions?

 

[Alex Blakenburg]

I am afraid if you are new to Excel functions and formulas you starting at the deep end.
Pre-365 the formula gets a lot more complicated and looks something like the below.

Each condition (=, >, < etc) expression needs to be divided by itself to force the FALSE conditions to produce an error.
The final multiplier in the conditions section is the row number expression ( (ROW($A$5:$A$14)-ROW($A$4))) since that is what you need as the result in the row position of the Index function.

If you want us to give you the exact formula, you would need to provide use with an XL2BB of your data.
Where the formula is in relation to the data and in relation to B66, B50, C60, H4 ?

In the below I4 is one of my criteria and I have the formulas starting in I5

=IFERROR(@INDEX($B$5:$B$14,
         AGGREGATE(15,6,
               ($A$5:$A$14=$I$4)/($A$5:$A$14=$I$4)*
               ($D$5:$D$14>DATEVALUE("30/10/2021"))/($D$5:$D$14>DATEVALUE("30/10/2021"))*
               ($D$5:$D$14<DATEVALUE("10/11/2021"))/($D$5:$D$14<DATEVALUE("10/11/2021"))*
               (ROW($A$5:$A$14)-ROW($A$4)),
               ROWS($I$5:$I5))),"")