Возвращение матча # N/A

Question

У меня есть значение в ячейке G5:

I~W478i~100

И массив в F31:F43:

I~W478i~100
II~W707a~79
I~W621c~97.5
II~W693a~81.5
I~W452n~80
I~W693a~71.5
II~W707a~64
I~W478i~75
II~W724a~100
I~W707a~79
I~W693a~91.5
I~W587a~69
III~W724a~75

Я формула MATCH для определения позиции значения в массиве:

=MATCH(G5,F31:F43,0)

Это возвращается # N/A.

Я считаю, что значение в F31 должно совпадать со значением в G5. Чтобы перепроверить, я создаю другую формулу:

=IF(G5=F31,TRUE,FALSE)

Что возвращает ИСТИНА. Однако функция MATCH по-прежнему возвращает # N/A. Я проверил и значение и массив, и оба типа данных "Общие".

Каковы возможные причины этой проблемы?

Answer 1

Проблема в том, что символ «~» является специальным символом, когда используется в первом параметре функции MATCH . Он используется для обозначения того, что символ, следующий за «~», не является символом подстановки (см. Документацию). Если вы хотите, чтобы первый параметр содержал символы «~», вам нужно использовать два символа «~».

Поэтому, если вы измените значение в G5 с I~W478i~100 на I~~W478i~~100 , ваша функция MATCH вернет 1 вместо # N/A.

Как отмечает Скотт Крэнер (спасибо Скотту) в комментарии к вопросу, вы также можете оставить G5 как есть и использовать SUBSTITUTE чтобы вставить дополнительные символы «~» как часть формулы.

=MATCH(SUBSTITUTE(G5,"~","~~"),F31:F43,0)

Обратите внимание, что если значения в F31:F43 также могут содержать "*" или "?" символы, вам также нужно будет поменять каждую "*" на "~*" и каждую "?" до "~?"