Excel - поиск значений сначала по столбцам, чем по строкам из найденного столбца

Question

У меня есть таблица выше.

Сначала мне нужно искать столбцы, основываясь на диаметре трубы. Например, мне нужно найти 3/4 "трубу. (Это находится в столбце D).

Во-вторых, мне нужно найти строки из этого найденного столбца (столбец D) для дебета по этой трубе.

Например, дебет 0,67. Как видно из таблицы, в столбце D нет дебета = 0,67, поэтому мне нужна функция, которая возвращает мне значения 0,63 (ячейка D5) и 0,77 (ячейка D6) в двух разных полях.

Есть ли функция (комбинация функций), которую я могу использовать?

Мой второй пример: диаметр для поиска 1 1/4 ", дебет 4.4. Диаметр 1 1/4" можно найти в столбце F. Дебет 4.4 отсутствует в столбце F, поэтому мне нужны значения 4.3 (ячейка F9) и 4.59. (ячейка F10) для возврата.

Я пытался = INDEX($ AG $ 2:$ AT $ 31, MATCH(W4, $ AG $ 2:$ AG $ 31,0), MATCH(Y4, $ AG $ 2:$ AT $ 2,0)), но эта лань не возвращает мне нужное значение. Также я попытался = SUBSTITUTE(ADDRESS(1, MATCH($ Y6, $ A $ 1:$ N $ 1,0)), "", ""), чтобы сначала получить необходимый столбец, а затем каким-то образом получить значения строк с помощью так же нет успеха.

Answer 1

@Rajesh SI выкладываю здесь два снимка экрана согласно вашему запросу.

Answer 2

Вы почти получили это. MATCH() может искать значение только в одной строке или столбце. INDEX() может извлечь значение из таблицы, и обычно вы используете MATCH(), чтобы указать номер строки и номер столбца, ища заголовок правой строки и столбца.

Ваша проблема немного сложна, потому что у вас нет заголовков строк, которые MATCH() может использовать. Таким образом, вам нужно (сначала) получить соответствующий столбец, а затем сопоставить число в этом столбце.

Давайте построим формулу по частям, и будет легче увидеть, что происходит. INDEX(array,row_num,col_num) вернет столбец, все, что вам нужно сделать, это пропустить row_num. Скажем, диаметр трубы, который вы ищете, указан в B25, как на скриншоте ниже. Тогда эта формула вернет соответствующий столбец таблицы данных:

INDEX(B2:J20,,MATCH(B25,B1:J1,0))

Вы можете проверить это, выделив формулу в строке формул и нажав F9. Это покажет вам список значений в столбце с заголовком B25.

Далее мы хотим найти дебет в этом столбце, поэтому мы используем столбец в формуле MATCH() :

MATCH(C25,INDEX(B2:J20,,MATCH(B25,B1:J1,0)),1)

Обратите внимание, что для этого внешнего MATCH() мы используем match_type из 1 вместо 0. Match_type 0 вызывает точное совпадение, но match_type 1 возвращает ближайшее нижнее значение, если не может найти точное совпадение. И это именно то, что вы хотите.

Теперь единственная проблема состоит в том, что эта формула просто возвращает позицию найденного совпадения, а не фактическое значение. Но теперь мы знаем столбец и строку, поэтому мы можем заключить их в другой INDEX() чтобы получить фактическое значение дебета:

=INDEX(B2:J20,MATCH(C25,INDEX(B2:J20,,MATCH(B25,B1:J1,0)),1),MATCH(B25,B1:J1,0))

Здесь последний MATCH() определяет col_num, а первый указывает row_num. Эта формула в E24 на картинке ниже. Он не нашел точного соответствия для дебета 1,7, поэтому он вернул следующее более низкое число 1,59.

Мы можем получить следующий более высокий дебет, просто добавив 1 к row_num:

=INDEX(B2:J20,MATCH(C25,INDEX(B2:J20,,MATCH(B25,B1:J1,0)),1)+1,MATCH(B25,B1:J1,0))

И мы можем сделать так, чтобы он возвращал пробел, если было найдено точное совпадение:

=IF(F24<>"Match",INDEX(B2:J20,MATCH(C25,INDEX(B2:J20,,MATCH(B25,B1:J1,0)),1)+1,MATCH(B25,B1:J1,0)),"")

Формулы в F24 и F25:

=IF(E24=C25,"Match","Next Lower")

=IF(E24=C25,"","Next Higher")

Надеюсь, это поможет. Удачи.

РЕДАКТИРОВАТЬ: чтобы вернуть соответствующие значения из столбца A, мы можем использовать OFFSET(). Смещение начинается с указанной ячейки (здесь мы будем использовать A1) и уменьшается на указанное количество строк. Для количества строк мы будем использовать то же выражение, которое мы использовали для получения позиции дебета: MATCH(C25,INDEX(B2:J20,,MATCH(B25,B1:J1,0)),1)

Вся формула:

=OFFSET(A1,MATCH(C25,INDEX(B2:J20,,MATCH(B25,B1:J1,0)),1),0)

И мы можем получить следующее более высокое значение, просто добавив 1 к числу строк, как и раньше:

=OFFSET(A1,MATCH(C25,INDEX(B2:J20,,MATCH(B25,B1:J1,0)),1)+1,0)

Answer 3

Ваша проблема может быть решена с помощью двустороннего поиска. Вам нужно найти значение, где критерии указывают столбец и строку.

Проверьте снимок экрана ниже.

Приведенная ниже формула находит значение в строке, начинающейся с 0,04, и в столбце, начинающемся с 3/4 ". Результат 0,63.

=INDEX(B2:E10,MATCH(A5,A2:A10,0),MATCH(D1,B1:E1,0))

Обратите внимание, если мое решение не то, что вы ищете, просто прокомментируйте ниже.

Надеюсь, это поможет.