Избегать НЕПОСРЕДСТВЕННЫХ ()

Question

У меня есть данные в строке № 3, как:

и я хочу найти первые два вхождения слова Pass.

Первое появление легко; в C6 я ввожу:

=MATCH("Pass",3:3,0)

и это правильно дает 3 (столбец C). Чтобы получить позицию второго вхождения, я хочу использовать MATCH(), начиная с одного столбца справа от первого вхождения, то есть D3: IV3, но на основании результата, который я уже имею в C6, я знаю, что могу получить D с:

=SUBSTITUTE((LEFT(ADDRESS(1,C6+1),3)),"$","")

Поэтому я использую:

=MATCH("Pass",INDIRECT(SUBSTITUTE((LEFT(ADDRESS(1,C6+1),3)),"$","") & "3:IV3"),0)+C6

Это работает:

Но я не могу поверить, что эта крайне уродливая формула - лучший способ найти второе вхождение. Какие-либо предложения??

Answer 1

Используйте AGGREGATE как МАЛЕНЬКИЙ (IF()), чтобы получить второй столбец с Pass:

=AGGREGATE(15,6,COLUMN(A:Z)/(A3:Z3="Pass"),2)

куда 2 это происшествие.

---

Я знаю, насколько вам не нравятся формулы массива, так как вы уже знаете первое, вы можете найти второе с помощью этой формулы:

=MATCH("Pass",INDEX(3:3,D5+1):INDEX(3:3,15000),0)+D5