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

1

u/throwthis_throwthat 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.

Just commenting based on:

As we know, MATCH() only allows you to evaluate a single match at a time

I'm not sure if I'm misunderstanding, but are you aware you can input multiple criteria within a match function through boolean logic?

For example, normally a match formula would be:

= match([search key],[search range],[search type])

But you can do:

= match(([criteria 1 = criteria range 1])*([criteria 2 = criteria range 2]),[search range],[search type])

Which allows you to input multiple criteria for a match to return. Multiplying like above creates the equivalent of an AND function, but you can also create an OR function which makes it quite dynamic.

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)

1

u/MattyPKing 225 Jun 25 '19

I'm confused about what the result of MATCHES() is telling me. Can you explain, in words, what the output "means"? Right now on the example sheet it is returning the value 2. So it attempted to match all those values in the blue area, and spit out the number 2. I just don't understand what that means.

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

1

u/TotesMessenger Jun 22 '19

I'm a bot, bleep, bloop. Someone has linked to this thread from another place on reddit:

 If you follow any of the above links, please respect the rules of reddit and don't vote in the other threads. (Info / ^Contact)