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.


1 Answer 1


Try the filter() function, like this:

  '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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.