filter with multiple regexmatch?

Question

my spreadsheet link with edit authorized - feel free to test any formula

https://docs.google.com/spreadsheets/d/1iY0p3_mdOfrjtBy9HPskzudf27m1mh_hApecsn0KW4A/edit#gid=1268421551

please look at the country that has statename such as P26-P38

I have 2 sheets - one is main sheet and another is named 'articles'

MAIN SHEET

ARTICLES

MAIN SHEET > on column P > I need to show the maximum date value located in column 'F' [articles!F:F] from 'articles' sheet by finding any rows on column 'B' of 'articles' [articles!B:B] that contains the number from column 'M' of 'Main sheet' [main!M:M] so I use this formula

=IFERROR(MAX(filter(articles!F:F,regexmatch(","&articles!B:B,","&M58&","))))

and its working fine

but then I also need to find state name which is located in 'column E of articles'[articles!E:E]

like in the screenshot above I need to find 'Alberta' for Canada as you can see the state name will come after '-' and follow by ':'

So it need to match both conditions and return the date

I don't know how can I define it in the formula 'regexmatch' and combine it together with the formula that I already have

I tried this but doesn't work

=IFERROR(MAX(filter(articles!F:F,regexmatch(","&articles!B:B,","&M58&","),articles!E:E,"-"&" "&K58&":")

anyone please help, thanks

JPV JPV · Accepted Answer · 2018-04-25T06:21:11

See if this works

=IFERROR(MAX(filter(articles!F:F,regexmatch(articles!B:B&articles!E:E, M59&K58&":"))))

If not, please share a copy of your spreadsheet with editing rights.

EDIT: after seeing the data, try (e.g. in P26)

=IFERROR(MAX(filter(articles!F:F,regexmatch(","&articles!B:B,","&M26&","), regexmatch(articles!E:E,K26))))

Drag up or down as far as needed.

filter with multiple regexmatch?

1 Answers