Google-sheets – Google Sheets pulling more data than the criteria specified

google sheetsgoogle-sheets-arrayformula

I have a tab in my Google Sheet for my raw data. I have another tab which has the report form which includes to criteria fields which I want to be able use to pull the appropriate data from the raw data tab. I am using "INDEX" and "MATCH" along with my two criteria fields to extract the records in my report. Everything appears to work on the first criteria field by fails on the second criteria field in that it pulls all future dates greater then the date set in the criteria field.

My formula is
{=ArrayFormula(INDEX('Traffic Discrepancy Log'!$A1:$I,MATCH(1,('Traffic Discrepancy Log'!B:B=C$2) * ('Traffic Discrepancy Log'!D:D=C$1),0),0))}

My raw date looks like this:

enter image description here

The report returns this:

enter image description here

Note that although I have the date in my criteria set for 4/28/2021 the report lists 4/29/20, 4/30/2021 and 5/01/2021. Ideally I only want to display the date that aligns with the criteria in cell "c1" of the report tab.

Any suggestions on how to resolve this would be appreciated.

Best Answer

Try the filter() function, like this:

=filter( 
  'Traffic Discrepancy Log'!A1:I, 
  'Traffic Discrepancy Log'!B1:B = C2, 
  'Traffic Discrepancy Log'!D1:D = C1 
)

This is an array formula that creates the whole result table in one go.

Related Topic