2

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 INDEX() or 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 *, ?, and ~. 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.

2

You can use aggregate:

=AGGREGATE(15,6,(ROW(IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]])-MIN(ROW(IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]]))+1)/(IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]]=[@[ITEM_CODE]]),1)

enter image description here

Or you can use this array version of MATCH:

=MATCH(TRUE,[ITEM_CODE]=IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]],0)

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter when exiting Edit mode.

enter image description here

This is the same as the second but does not require the need for CSE:

=MATCH(TRUE,INDEX([ITEM_CODE]=IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]],),0)
  • In the third expression, [ITEM_CODE]=IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]] looks to me like an array formula. But it doesn't require CSE? – MetaEd Sep 21 '18 at 18:41
  • Neither the first nor the third require CSE even though they are array type formulas. They are native array formula. There are a few of those; SUMPRODUCT,IRR,... and a few others. – Scott Craner Sep 21 '18 at 18:44
  • Do you know where in the Microsoft docs I can find out which functions create this sort of array formula context? – MetaEd Sep 21 '18 at 19:03
  • No, I do not, sorry. I learned them organically. – Scott Craner Sep 21 '18 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.