I currently use
MATCH() in Excel 2016 formula columns such as:
= MATCH( [@[ITEM_CODE]], IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]], 0 )
The result is essentially a foreign key. It is used in other columns with
OFFSET() expressions to relate the current row to the matching row in the other table.
Some lookup values contain punctuation, including the special wildcard characters
~. This causes unintended matching. A real example is the item code
*XA1, which unintentionally matches item code
1SC0021REXA1 in the other table.
My current solution for the example above is:
= MATCH( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( [@[ITEM_CODE]], "~", "~~" ), "*", "~*" ), "?", "~?" ), IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]], 0 )
What don’t I like? It’s easy to forget when writing
INDEX(MATCH()) expressions, easy to get wrong (order matters), and just plain clunky and hard to read. But I have not found a non-wildcarding
MATCH() alternative, or even a way to simplify the substitution expression, without creating more complexity elsewhere, such as writing a custom function in VBA.