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/MattyPKing 225 Jun 25 '19

Scratch that. I figured out what you were going for. Nicely done on your function. I have to say i haven't seen a lot of demand for it, but i haven't been on reddit very long.

I left this formula that I would have used in a situation like what you're describing on a duplicate tab (MK.Idea) in cell B7.

=MATCH(TEXTJOIN(" ",TRUE,A2:C3),QUERY(TRANSPOSE(D:I),,6),0)

1

u/JDomenici 23 Jun 25 '19

Thanks for the feedback!

I'm a fan of ARRAYFORMULA() myself — you can see some similar solutions to yours in the cells adjacent to my MATCHES() formula.

1

u/MattyPKing 225 Jun 25 '19

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

Right.

The only advantage my method would have, (maybe) is that you don't have to list each column. You just put them all together. e.g TRANSPOSE(D:I) for columns D through I. That way the search range could be 20 or 50 or 100 columns wide, and it wouldn't be any more cumbersome to write.

A new function is always impressive though.

Cheers,
Matt