Search code examples
google-sheetsarray-formulas

Google Sheet - Find partial match between ranges / columns


On Google Sheet, given two ranges / columns of data, lookup column A based on its partial match on column B, show empty if no matches.
Particularly in my case, given a list of blocks and a list of rooms with block prefix, identify the block for each room.

+--------+---------+------------------------+
| Block  |  Room   | Block of Room (Output) |
+--------+---------+------------------------+
| AD     | AD201   | AD                     |
| AE     | AD208/3 | AD                     |
| HG     | ADG07   | AD                     |
| HH     | ADUG8   | AD                     |
| HSH    | BY03    |                        |
|        | HG03    | HG                     |
|        | HGG01   | HG                     |
|        | HSH01   | HSH                    |
|        | HSHG5   | HSH                    |
+--------+---------+------------------------+

I tinkered with search(), index() and match() like this: =IFERROR(if(search(index($A$2:$A,MATCH(B2,$A$2:$A,1),1), B2), index($A$2:$A,MATCH(B2,$A$2:$A,1),1), ""))
but it didn't work well with arrayformula().

Finally I come up with a solution like this
=ARRAYFORMULA( iferror(IF(SEARCH( VLOOKUP(B2:B,A2:A,1), B2:B), VLOOKUP(B2:B,A2:A,1), "")))

I wonder if there exists more elegant ways to do the matching.


Solution

  • =ARRAYFORMULA(IFERROR(REGEXEXTRACT(B2:B,"^("&TEXTJOIN("|",1,A2:A)&")")))
    
    • JOIN blocks by |(= or in regex)
    • Use that regex to extract block from room
    • "^" represents start of string

    =ARRAYFORMULA(VLOOKUP(LEFT(B2:B10,2)&"*",A2:A10,1,0))
    

    Uses two characters from LEFT of Col B to lookup in A and return result. This should be faster than regex, but maybe a little less accurate.