Index&Match inc date range query

katemc

New Member
Joined
Nov 26, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
 
Last edited by a moderator:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,084
Office Version
  1. 365
Platform
  1. Windows
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

Excel Formula:
=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))),"")
 

Forum statistics

Threads
1,186,124
Messages
5,955,975
Members
438,230
Latest member
Meemaw2212

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top