Как найти все подходящие цифры / данные в диапазоне

Question

Я пытаюсь найти МАКСИМАЛЬНУЮ цифру и год, с которым она может быть связана.

• ЛЕТ 2000-2010 (А1:J1)
• ДАННЫЕ (A2:J2)
• МАКС ДАННЫХ помещается в А3

Я могу использовать INDEX/MATCH, чтобы дать мне ПЕРВЫЙ МАКС. ГОД, где он соответствует МАКС. ДАННЫХ, но эта МАКС. Цифра может появиться через несколько лет (например, 2002, 2005, 2009).

ПЕРВЫЙ МАКС. ГОД в B3

У меня есть формула в C3, которая теперь пытается начать другое сопоставление индексов, используя (OFFSET, 0, B3), чтобы начать поиск после ПЕРВОГО МАКСИМАЛЬНОГО ГОДА, но это дает мне первый год, когда он встречается по какой-то причине.

=INDEX(A1:J1,MATCH(A3,OFFSET(A2:J2,0,MATCH(B3, A1:J1,0))))

Я округлил свои цифры, чтобы у них был один десятичный знак, и я знаю, что в моем диапазоне A2:J2 есть две совпадающие цифры MAX of DATA.

Обновление: изображение, которое мы надеемся объяснить более четко.

Есть идеи?

Спасибо Майкл

---

ОТВЕТ:

Я проследовал по ссылке Абдула в комментариях ниже и нашел формулу, которая очень хорошо отвечала моим требованиям.

= ЕСЛИОШИБКА (ИНДЕКС (А1: К1, МАЛЫЙ (ЕСЛИ (А2: К2 = $ A $ 3, столбец (А2: К2)-COLUMN (ИНДЕКС (A2: K2,1,1))+1), п-й)),» «)

N-я ячейка № 2. Так что он ищет второй случай моей ячейки A3.

Надеюсь, что это помогает людям. И Exceljet - отличный сайт. Очень рекомендую.

получить-п-й матч-с-индекс-матч

Answer 1

Это можно сделать с помощью формул, введенных только в B3, C3 и т.д. Но я предлагаю добавить вспомогательную строку (строка 4), чтобы сделать формулы более читабельными.

В ячейку B4 введите:

=MATCH($A3,OFFSET($A2:$K2,0,A4),0)+A4

Это возвращает 6 который является номером столбца первого вхождения 19.0 в строке 2. Скопируйте эту формулу справа, и вы получите 8 и 11 а затем #N/A в остальных ячейках. Теперь введите в ячейку B3:

=INDEX($A1:$K1,B4)

И скопируйте формулу справа. Вы получите годы, в которые было найдено максимальное значение: 2005 , 2007 и 2010 затем #N/A в остальных ячейках.

Если вы не хотите видеть значения #N/A , вы можете использовать функцию IFERROR чтобы заменить их пробелами.

Answer 2

Мне нравится ответ Блэквуда, так как он не требует формулы массива. Но если вы не можете придерживаться строки помощника или вам нужно сделать это только с одной формулой, вы можете использовать это в A4 и заполнить справа:

 =IFERROR(SMALL(IF($A$2:$K$2=$A$3,$A$1:$K$1),COLUMN()),"")

Это формула массива, поэтому ее нужно вводить с помощью CTRL Shift Enter, а не просто Enter.

Вот как это работает. IF() создает массив лет, когда данные соответствуют A3 (максимум данных) и FALSE везде:

{False;False;False;False;False;2005;False;2007;False;False;2010}

Затем SMALL() выбирает 1-е, 2-е, 3-е и т.д. Наименьшие значения из этого массива, используя номер столбца в качестве индекса. Он проверяет только числа и игнорирует "ложные" значения в массиве.

Наконец, IFERROR() заменяет любые ошибки # N/A на пустые.