Advanced Criteria range with 3 data elements

Question

Advanced Criteria range with 3 data elements

Joe Schwartz 0

I trying to setup an advanced filter criteria range. I need 3 columns 1)DATE has to match any of the date values and both of the values in the other 2 columns. IE date of 01, 02 or 03, Code of "R", State of "PA"

I have never attempted something like this. Don't know how to set it up. I am open to 1 extract to a secondary range and a second extra from there to my report.

Thanks

Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-08-31T23:06:28.0666667+00:00

Hi,

Please share some data to work with and mention the exact criteria.
Joe Schwartz 0 Reputation points

2026-08-31T23:32:52.9033333+00:00

I tried to copy something in but I couldn't get it to work but here goes: Macro will insert variables into the criteria range. So source data has to have one of the date values and the Code and the state.

Date Code State

01-2026 R PA

02-2026

03-2026

Hope this helps
Rory Archibald 19,050 Reputation points Volunteer Moderator

2026-09-01T10:57:11.1666667+00:00

I'd suggest using a formula criteria range. To use that you need a header cell that is either blank or has a title that does not match any columns in your data. The formula you put in the cell below that needs to return True or False using a relative reference to the first row of data you are filtering.

In order to be more specific, we'd need to know where the data to be filtered is (rows and columns) and where (which cells) you are entering the criteria values with your code.

2 answers

Your answer

Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-08-31T23:06:28.0666667+00:00

Hi,

Please share some data to work with and mention the exact criteria.
Joe Schwartz 0 Reputation points

2026-08-31T23:32:52.9033333+00:00

I tried to copy something in but I couldn't get it to work but here goes: Macro will insert variables into the criteria range. So source data has to have one of the date values and the Code and the state.

Date Code State

01-2026 R PA

02-2026

03-2026

Hope this helps
Rory Archibald 19,050 Reputation points Volunteer Moderator

2026-09-01T10:57:11.1666667+00:00

I'd suggest using a formula criteria range. To use that you need a header cell that is either blank or has a title that does not match any columns in your data. The formula you put in the cell below that needs to return True or False using a relative reference to the first row of data you are filtering.

In order to be more specific, we'd need to know where the data to be filtered is (rows and columns) and where (which cells) you are entering the criteria values with your code.

Answer 1

You can use the match function to verify the date in question matches one of the desired dates. The other tow criteria a simple equality checks. You combine the three criteria using multiplication.

For example, if the desired dates are in A1:A3, the first specified value is in B1, the second specified value is in C1, and the array to be filtered is in F1:H6, the the formula you want is

=FILTER(F1:H6,(IFERROR(MATCH(F1:F6,A$1:A$3,0),0))*(--(G1:G6=B$1))*(--(H1:H6=C$1)))

MATCH verifies the date is one of the acceptable ones.

IFERROR replaces the non-match value with 0

The two equality expressions convert TRUE/FALSE to 1/0.

The product will be nonzero only if all three criteria are satisfied. Those are the rows that FILTER will select.

Answer 2

In Excel Advanced Filter, the criteria range works as AND across columns and OR down rows. So, if you want Date = 01-2026 OR 02-2026 OR 03-2026, while Code should be R and State PA, your criteria range should be:

Date Code State 01-2026 R PA 02-2026 R PA 03-2026 R PA

This tells Advanced Filter: Date is 01-2026 AND Code is R AND State is PA, OR Date is 02-2026 AND Code is R AND State is PA, OR Date is 03-2026 AND Code is R AND State is PA.

You cannot put the three dates in separate cells under the Date column while putting R and PA only once. Excel interprets each row as a separate complete criteria set, so R and PA need to be repeated on every date row.

If your "Date" values are actual Excel dates rather than text such as "01-2026", there is an additional issue because Excel stores dates as complete dates, such as 1/15/2026. In that case, the criteria should use date ranges rather than simply 01-2026.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin