Excel: формулы MATCH() без подстановочных знаков

Question

В настоящее время я использую MATCH() в столбцах формул Excel 2016, таких как:

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

Результат по сути является внешним ключом. Он используется в других столбцах с выражениями INDEX() или OFFSET() чтобы связать текущую строку с соответствующей строкой в другой таблице.

Некоторые значения поиска содержат знаки препинания, включая специальные символы подстановки * ? и ~ . Это вызывает непреднамеренное соответствие. Реальным примером является код товара *XA1 , который непреднамеренно соответствует коду товара 1SC0021REXA1 в другой таблице.

Мое текущее решение для примера выше:

= MATCH(
SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( [@[ITEM_CODE]], "~", "~~" ), "*", "~*" ), "?", "~?" ),
IM_PURCH_VENDOR_SUBLIST[[#All],[ITEM-CODE]],
0
)

Что мне не нравится? Легко забыть при написании выражений INDEX(MATCH()) , легко ошибиться (порядок имеет значение) и просто неуклюже и трудно читаемо. Но я не нашел альтернативы MATCH() без подстановочных знаков или даже способа упростить выражение подстановки, не создавая при этом сложности в других местах, таких как написание пользовательской функции в VBA.

Answer 1

Вы можете использовать агрегат:

=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)

Или вы можете использовать эту версию массива MATCH:

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

Будучи формулой массива, она должна быть подтверждена с помощью Ctrl-Shift-Enter при выходе из режима редактирования.

Это то же самое, что и второе, но не требует использования CSE:

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