Index/Match - в таблице, содержащей несколько одинаковых значений

Question

[! [введите описание изображения здесь] [2]] [2]

Цель:

Используйте известное значение "length", чтобы найти правильный заголовок столбца, затем найдите значение в столбце, а затем верните индекс справа от этой таблицы данных.

Фон:

1. Некоторые значения в теле таблицы повторяются в разных столбцах
2. Значения в теле таблицы представляют нижние пределы интервалов
3. Заголовки столбцов от C4 до F4 представляют нижние пределы интервалов
4. Заголовки строк от B5 до B8 каждый представляют уникальный индекс

ТАБЛИЦА:

A   B   C   D   E   F   G   H   I   J   K               
3           Length                      Header                  
4       **4.1   5   5.3 6**     Length: 5.4 5.3 =HLOOKUP($J$4;C4:F4;1)          
5   **7**   18  20  21  23      Strength    28              
9   **20**  22  23  25      Index   11  =INDEX(B5:B8;MATCH(J5;E5:E8;4))                 
7   **11**  24  25  27  28                                  
8   **16**  27  28  30  31  

Уже достигнуто:

1. Информация в J4 и J5 всегда дается (то есть известна)
2. HLOOPKUP (J4) используется для определения правильного заголовка столбца - и правильный результат появляется в K4, т.е. столбец E
3. Формула (INDEX/MATCH) в J6 затем корректно ищет значения в заголовке столбца E (E5:E8 - которые я должен ввести) -
   и правильно идентифицирует, что "28" (J5) попадает в интервал "27" (E7) - и затем правильно идентифицирует соответствующий заголовок строки как "11" (B7)

Требуется помощь:

1. В настоящее время я сначала должен запустить формулу HLOOKUP, чтобы определить заголовок столбца
2. Затем я должен вручную ввести значения столбца (исключая заголовок столбца) - на основе результата K4, в формулу в J6, чтобы запустить функцию INDEX/MATCH
3. Поэтому мне нужна помощь, чтобы автоматически вставить значения столбца (исключая заголовок столбца) на основе результата HLOOKUP в формулу INDEX/MATCH.

4. Я бы предпочел использовать функции, а не VBA, если это вообще возможно

   [Снимок экрана с ошибками] [1] На снимке экрана показано, как некоторые вычисления дают результат # N/A.

С уважением. Rudi

Answer 1

Используйте OFFSET чтобы сместить диапазон от самой верхней левой ячейки, чтобы получить правильный диапазон.

=INDEX($B$5:$B$8, MATCH($J$5, OFFSET($C$5, 0, MATCH($J$4, $C$4:$F$4, 1)-1, ROWS($B$5:$B$8), 1), 1))

объяснение

Это ведет себя так же, как ваша формула INDEX MATCH , однако я изменил E5:E8 в вашем примере на относительный диапазон, определенный с помощью OFFSET( ... )

OFFSET($C$5, //Reference point
       0,    //Rows shifted below, in this case it doesn't shift the row
       MATCH($J$4, $C$4:$F$4, 1)-1, //Column shifted to the right, in this case it shifts from `1-4` minus 1.
       ROWS($B$5:$B$8),           //Height, in this case you want as many rows as the table has, taken from the left "header"
       1)    //Width, in this case you only want one column

---

$C$5 - это самая верхняя левая ячейка ваших значений, которая используется в качестве контрольной точки.

MATCH($J$4, $C$4:$F$4, 1)-1 - для получения индекса заголовка столбца. Это похоже на формулу HLOOKUP , но вместо этого она возвращает номер столбца (например, 3-й столбец возвращает 2)

ROWS($B$5:$B$8) - это количество строк в таблице, взятое из количества строк в левом "заголовке"