Формула Excel для возврата значения из сетки

Question

У меня есть некоторые данные, которые выглядят примерно так

A B C D E F G
B C D E F G A
C D E F G A B
D E F G A B C 
E F G A B C D
F G A B C D E 
G A B C D E F 

Кроме того, у меня есть часть данных, которая выглядит следующим образом:

A 
B 

Что мне нужно для поиска, так это заголовок строки, в которой находится верхнее значение, когда заголовок столбца является нижним значением. Например, в этом примере я бы посмотрел в первой строке, чтобы найти B , затем я посмотрел бы вниз по столбцу, пока не нашел A , затем я бы посмотрел на заголовок строки, чтобы вернуть G

 A  (B) C D E F G
 B   C  D E F G A
 C   D  E F G A B
 D   E  F G A B C 
 E   F  G A B C D
 F   G  A B C D E 
(G) (A) B C D E F 

Я пытался использовать HLOOKUP , INDEX , MATCH и т.д., Но я продолжаю сталкиваться с препятствиями, когда мне нужно предоставить массив для всего столбца.

Вот один такой пример синтаксиса, который дает мне ошибки:

=CONCAT(address(8,1):address(12,1))

У меня не может быть : между моими двумя сотовыми адресами.

Есть идеи?

Answer 1

Я уверен, что есть более элегантные способы сделать это, но вот быстрое и грязное решение.

Я застрял вашу сетку в произвольном месте (B2:H8). J2 и J3 - ваши значения поиска, а результат - в J4. То, что находится в столбце L, является компонентами ответа для целей объяснения, потому что формула становится длинной.

Первая часть находится в L3, находя цель B в верхнем ряду:

=MATCH(J3,B2:H2,0)

Он находит цель B в столбце 2 массива, и это используется на следующем этапе поиска A в этом столбце. L2 содержит:

=MATCH(J2,OFFSET(B2,0,L3-1,7,1),0)

OFFSET привязан к верхнему левому углу массива (B2). Столбец, в котором был найден B был вторым столбцом массива, но это смещение 1 от первого столбца, следовательно, -1 (мне нравится, когда я использую "следовательно" в предложении). OFFSET позволяет указать диапазон, используя параметры высоты и ширины. 7 - это высота массива в этом примере.

MATCH находит цель A в строке 7 этого столбца массива.

Теперь вам просто нужно перейти к этой строке первого столбца массива:

=INDEX(B2:B8,L2,0)

Там вы найдете свой ответ: G

Вы можете заменить формулы компонентов для компонентов, чтобы получить автономную автономную формулу:

=INDEX(B2:B8,MATCH(J2,OFFSET(B2,0,MATCH(J3,B2:H2,0)-1,7,1),0),0)