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.
=ARRAYFORMULA(IFERROR(REGEXEXTRACT(B2:B,"^("&TEXTJOIN("|",1,A2:A)&")")))
|
(= or in regex) =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.