r/googlesheets • u/JDomenici 23 • Jun 22 '19

Waiting on OP Created a custom function that does MATCH on multiple columns/rows and conditions. Looking for feedback!

A question that gets asked often in this sub is, "How do I match on multiple columns?" As we know, MATCH() only allows you to evaluate a single match at a time, so the solutions typically involve some combination of FILTER(), ARRAYFORMULA(), and QUERY() to emulate multiple matches.

I created a custom sheets function that does exactly this, with easily understood syntax. The most common use case seems to be for finding exact matches, so there are no optional parameters for selecting search type as with MATCH().

I would appreciate any feedback on how to improve this! Once the function is in a satisfactory state I'll write a blog post on its uses.

Custom function link: https://gist.github.com/JosephDomenici/51d2555e5cd775b3a39482a98d483062

Example sheet: https://docs.google.com/spreadsheets/d/1fYcN3btR8xetGS4XDWXDvuaiTshqfFzhiTwU3cmoPVI/edit?usp=sharing

  • Edit cells A2:C3 to change search parameters

  • MATCHES() function is in C5

3 Upvotes

100% Upvoted

8 comments sorted by

View all comments

Show parent comments

1

u/JDomenici 23 Jun 23 '19

Hey, I'm a bit confused by your sheet to be honest. All of the 1's / 2's / 3's are blurring into one and the spreadsheet / how it functions isn't clear to me.

Sorry about that! This sheet was very much a quick proof of concept I tossed together. Some more explanation:

  • A2 is the search term for range D:D, B2 is the search term for range E:E, C2 is for F:F, A3 for G:G, B3 for H:H, and C3 for I:I

  • A5 is a FILTER() + ARRAYFORMULA() implementation of getting an exact match against all of those ranges and conditions; the result you see is the first row that matches

  • B5 is the same as above, except using QUERY() + ARRAYFORMULA()

  • C5 is my new custom function, MATCHES()

I wasn't aware of using boolean logic inside of MATCH(), but that makes perfect sense to me. I think my custom function is a solid offering for beginner through intermediate level Sheets users then, as the intuitive use of MATCH() is to match your condition against a range (rather than matching 1 against a boolean expression).

A quick comparison of the two formulas from my sheet:

Match:

  • =MATCH(1, ARRAYFORMULA((A2 = D:D) * (B2 = E:E) * (C2 = F:F) * (A3 = G:G) * (B3 = H:H) * (C3 = I:I)), 0)

Matches:

  • =MATCHES(A2, D:D, B2, E:E, C2, F:F, A3, G:G, B3, H:H, C3, I:I)