Необычное поведение из формул Excel

Question

Мне удалось заставить следующее работать:

=IFERROR(INDEX(CUSIP2,SUMPRODUCT(ISNUMBER(SEARCH(CUSIP2,O2))*ROW($2:$5158))),"")

Что он делает, так это проверяет содержимое ячейки по списку, и если слово в списке появляется где-либо в ячейке, оно дает мне какое это слово. Большой! В идеале, это также дало бы мне пробел, если в камере нет совпадений. Здесь я сталкиваюсь с неприятностями.

Когда есть совпадение, оно отлично работает. Без совпадения будет выброшено случайное слово вместо пробела. Например, он дает мне SWPC12374 в ячейке «исходящий провод для КЛИЕНТА в БАНКЕ - смещенная запись не отправлена в СИСТЕМУ»

Теперь загадка углубляется. Брошенные неправильные слова всегда разные. Неправильные слова соответствуют тому, где они находятся в списке. Например, [WORD 1] добавляется в первую ячейку, где у меня есть уравнение, и оно должно быть пустым. [WORD 2] добавляется во вторую ячейку, где у меня есть уравнение, и оно также должно быть пустым. Так далее. [WORD 1] и [WORD 2] являются первыми двумя записями в списке CUSIP2, а другие неправильные записи являются соответствующими записями в списке CUSIP2.

Кроме того, они также находятся в одном ряду. Таким образом, первое уравнение встречается во второй строке, а первое слагаемое в списке встречается во второй строке.

Теперь, когда я углубляюсь в это с помощью решателя формул, он очень аккуратно заканчивается INDEX(CUSIP2,0) - затем выбрасывает неправильное слово, если оно не улавливается, и с радостью отправляет неверное слово.

Я схожу с ума, пытаясь отследить эту ошибку, и я не могу понять, почему Excel делает это. Пожалуйста помоги. Мой гугл-фу подвел меня (если я даже знал правильные термины для поиска этого)

Answer 1

Индекс Excel не любит 0 в качестве номера индекса. Вот пример, соответствующий вашему описанию:

Name 'Cups2'    INDEX(cups2,0)  INDEX(cups2,1)
hi              hi              hi
go              go              hi
now             now             hi
three           three           hi
five            five            hi
gal             gal             hi

Можете ли вы отредактировать свои данные так, чтобы первая запись в диапазоне CUSPID2 была ""? Затем вы можете изменить формулу, чтобы она возвращала эту первую запись, когда индекс равен 0:

=INDEX(CUSIP2,MAX(1,SUMPRODUCT(ISNUMBER(SEARCH(CUSIP2,O2))*ROW($2:$5158))))