Трехмерный поиск с помощью INDEX/MATCH

Ответ 1

Я использовал формулу IF() array для определения того, что число строк P было после строки George... Мне также понадобилось использовать функцию MIN(), чтобы получить первый P номер строки после имени.

Кроме того, это простая функция INDEX().... которая ломала мой мозг более часа:).

=INDEX($A$1:$D$9,MIN(IF((ROW(A1:A9)>MATCH($F$4,A1:A9,0))*(A1:A9=$F$5),ROW(A1:A9),"")),MATCH($F$6,$A$1:$D$1,0))

Не забывайте!
Используйте Ctrl+Shift+Enter при завершении формулы, поэтому ее оценивают как формулу array.

Ответ 2

Вы можете использовать два других INDEX/MATCH внутри первого MATCH для установки диапазона поиска. Затем вам просто нужно добавить MATCH(), чтобы найти абсолютное положение имени.

=INDEX(A:D,MATCH($H$4,INDEX(A:A,MATCH($H$3,A:A,0)):INDEX(A:A,MATCH($H$3,A:A,0)+4),0)+MATCH($H$3,A:A,0)-1,MATCH($H$5,$1:$1,0))

Ответ 3

Вы можете сделать это, просто добавив результаты двух совпадений. Одно совпадение имен и одно совпадение для буквы равно общей строке.

= ИНДЕКС (A: D, MATCH (G5, А3: A5,0) + MATCH (G3, А: А, 0), ПОИСКПОЗ (G4,1:1,0))

Другими словами: Индекс (все данные, совпадение (имя, колонка имени, точное) + совпадение (буква, колонка в столбце, точное), совпадение (имя столбца, строка столбца, точное)

Захват экрана рабочего листа

Ответ 4

Мой ответ пытается сделать общий случай только с одной оговоркой:

Что письмо - это текст с одним символом, а имя - более 1 символа. В противном случае я чувствую, что между буквами и именами нет никакой логики, и тогда это невозможно сделать..

RE-EDIT для лучшего построения функции:

{=INDEX($A$1:$D$17, MATCH($H$3,$A1:$A17, 0)+MATCH($H$4, INDEX($A1:$A17, MATCH($H$3,$A1:$A17, 0)):INDEX($A:$A, SMALL(IFERROR(MATCH($H$3,$A1:$A17, 0)+POWER(SQRT(IF(LEN($A$1:$A$17)>1, ROW($A$1:$A$17), 0)-MATCH($H$3,$A$1:$A$17, 0)), 2)-1, ROWS($A$1:$A$17)), 2)), 0)-1, MATCH($H$5, $A$1:$D$1, 0))}

Это использует формулу массива вдоль столбца A и проверяет, является ли длинa > 1 и выбрасывает число строк в массив, с буквами, заданными 0.

Затем строка соответствия уникального имени (например, George) вычитается из каждого.

Затем мы используем min (из всех других строк имен с последней строкой данных в качестве конечной функции по умолчанию - SMALL с параметром 2), чтобы найти следующую строку имени (или последнюю строку данных, если нет следующего имени).

Rest - это стандартный индекс/совпадение и т.д.

Он правильно вернет # N/A, если нет такого письма под выбранным именем...

Мой набор данных - A1: A17, и формула может использовать A: A вместо этого каждый раз, но для вычисления массива внутри IF требуется A1: A17 для скорости.

EDIT для лучшего построения функции:

Если бы мы хотели избежать редактирования формулы при изменении длины данных, то мы могли бы позволить полные ссылки столбцов A: A пройти всю конструкцию (и потерять скорость/эффективность) с последней строкой данных в коллайсе, вычисленной с помощью ROWS (А: А):

Re-редактирование:

{=INDEX($A:$D, MATCH($H$3,$A:$A, 0)+MATCH($H$4, INDEX($A:$A, MATCH($H$3,$A:$A, 0)):INDEX($A:$A, SMALL(IFERROR(MATCH($H$3,$A:$A, 0)+POWER(SQRT(IF(LEN($A:$A)>1, ROW($A:$A), 0)-MATCH($H$3,$A:$A, 0)), 2)-1, ROWS($A:$A)), 2)), 0)-1, MATCH($H$5,1:1, 0))}

Это действительно зависит от настройки...

Изменить снова для версии, которая принимает пробелы как разделители для имен

Если вы хотите использовать пробелы в качестве разделителя для имен, где в результатах данных нет пробелов, но пробелы отображаются в столбцах B-D, где есть имя, то небольшое изменение в приведенных выше формулах приведет к

=INDEX($A$1:$D$17, MATCH($H$3,$A$1:$A$17, 0)+MATCH($H$4, INDEX($A:$A, MATCH($H$3,$A:$A, 0)):INDEX($A:$A, SMALL(IFERROR(MATCH($H$3,$A:$A, 0)+POWER(SQRT(IF($B$1:$B$17="", ROW($A$1:$A$17), 0)-MATCH($H$3,$A$1:$A$17, 0)), 2)-1, ROWS($A$1:$A$17)), 2)), 0)-1, MATCH($H$5, $A$1:$D$1, 0))

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

Небольшая поправка к условию, чтобы найти конечный диапазон для поиска буквы, заменив это: SQRT(IF(LEN($A$1:$A$17)>1, следующим образом:

SQRT(IF($B$1:$B$17="",

Ответ 5

Я бы использовал область (4-й параметр) индекса(). Ниже приведен скриншот тестовых данных. В этом примере предполагается, что одни и те же столбцы и ключи сортируются и согласовываются.

Это работает, используя (Range1, Range2) в качестве первого параметра индекса. Для 4-го параметра индекса используйте N, для какой области в() вы хотите, чтобы индекс возвращался.

Ответ 6

Я думаю, что это может быть немного более аккуратным, и немного легче изменить, возможно.

=INDEX(OFFSET(INDIRECT("A"&MATCH($H$3,$A:$A,0),TRUE),0,0,4,4),MATCH($H$4,$A:$A,0),MATCH(H5,$1:$1,0))

Используя смещение, чтобы сначала создать диапазон, мы можем использовать имя из H3, чтобы установить это, а затем за ним мы просто индексируем в этом новом диапазоне.

Теперь это все еще зависит от пребывания в столбце A для имен.

Ответ 7

Предполагая, что формат данных всегда Name, то P, M и N эта формула выполняет работу:

=INDEX($A:$D,
MATCH($H$3,$A:$A,0)
+LOOKUP($H$4,{"P",1;"M",2;"N",3}),
MATCH($H$5,$1:$1,0))

Ответ 8

Это решение работает практически во всех условиях. Единственное ограничение, которое я обнаружил, - это когда один из субъектов (Имена) не имеет данных для каких-либо деталей (букв), но на данный момент то же самое происходит со всеми другими ответами.

Формула предполагает, что данные расположены в B6:F30 (чтобы обеспечить ее применение независимо от местоположения исходного диапазона).

Формула использует функции Index\Match:

Во-первых, MATCH для извлечения позиции Name:

MATCH($H8,$B$6:$B$30,0)

С этой информацией он использует INDEX для построения диапазона, который используется для получения позиции Detail (буквы) с использованием второй функции MATCH:

+ MATCH($I8,INDEX($B$6:$B$30, 1 + MATCH($H8,$B$6:$B$30,0))
:INDEX($B$6:$B$30,ROWS($B$6:$B$30)),0),

Добавление результатов первой и второй функций MATCH получает позицию комбинации Name `Detail` и использует ее в индексе для всех данных. Позиция требуемой колонки данных получается с помощью Match:

INDEX($B$6:$F$30, 1st.MATCH + 2nd.MATCH,
MATCH(J$6,$B$6:$F$6,0))

С результатами, расположенными в G6:L30, введите эту формулу в J8, затем скопируйте в J8:L30:

= INDEX( $B$6:$F$30,
MATCH( $H8, $B$6:$B$30, 0)
+MATCH( $I8, INDEX( $B$6:$B$30 , 1 + MATCH( $H8, $B$6:$B$30 ,0))
: INDEX( $B$6:$B$30, ROWS($B$6:$B$30) ),0),
MATCH( J$6, $B$6:$F$6, 0)),"")

Ответ 9

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

Эта формула предполагает, что данные расположены в B6:E30 (чтобы обеспечить ее применение независимо от местоположения исходного диапазона).

Эта формула использует функции Index\Match и ее массив формул.

Формулы-массивы вводятся нажатием [Ctrl] + [Shift] + [Enter] одновременно, вы увидите { и } вокруг формулы, если правильно ввести

Синтаксис:

=IFERROR(INDEX(DataRng,
MATCH(Value1,NamesRng,0)
+IFERROR(MATCH(Value2,INDEX(NamesRng,
1+MATCH(Value1,NamesRng,0))
:INDEX(NamesRng, IFERROR(MATCH(Value1,NamesRng,0)
+MATCH("#",IF((INDEX(Col1Rng,1+MATCH(Value1,NamesRng,0))
:INDEX(Col1Rng,ROWS(NamesRng)))="","#","!"),0),
ROWS(NamesRng))),0),NA()),MATCH(ValCol,DataHdr,0)),"")

Аргументы: Предполагая, что данные расположены на B6: E30.

Value1= Name можно найти в Data, то есть George, Scott и т.д.

Value2= Detail можно найти в Data, то есть Detail1, Detalle2 и т.д.

ValCol= Column можно найти в Data i.e Column1, Column2 и т.д.

DataRng= $B$6:$E$30

DataHdr= $B$6:$E$6

NamesRng= $B$6:$B$30

Col1Rng= $C$6:$C$30

1st MATCH: Получает позицию имени:

MATCH(Value1,NamesRng,0)

2nd MATCH: возвращает конечную позицию соответствующих им имен, которая определяется пустым значением в столбце C или в конце диапазона данных:

MATCH("#",IF((INDEX(Col1Rng, 1 + 1stMATCH)
:INDEX(Col1Rng,ROWS(NamesRng)))="","#","!"),0),

Создает диапазон (vRange): с информацией о наименованиях с использованием функций 1-го и 2-го совпадений. Если 2nd Match возвращает ошибку, то она использует последнюю строку диапазона данных:

INDEX(NamesRng, 1 + 1stMATCH )
:INDEX(NamesRng, IFERROR( 1stMATCH + 2ndMATCH, ROWS(NamesRng)))

3rd MATCH: извлекает позицию Detail в пределах vRange. Он возвращает #NA, если комбинация отсутствует.

IFERROR(MATCH(Value2, vRange,0), NA())

Добавление результатов 1-й и 3-й функций соответствия получает индекс строки Name `Detail combination or #NA if no found. The Column index is obtained with a Match from the Header of the Data. It then applying the INDEX function to the Data Range returns the value of the Имя\Подробно\Столбец combination. If the Имя\Деталь` не найден, он возвращает пустой.

=IFERROR( INDEX( DataRng, 1stMATCH + 3rdMATCH, MATCH(Column,DataHdr,0)),"")

С результатами, расположенными на H6: L37, введите этот массив формул в J8, затем скопируйте в K8: L37 и в J9: L37:

=IFERROR( INDEX($B$6:$E$30,
MATCH($H8,$B$6:$B$30,0)
+IFERROR( MATCH($I8, INDEX($B$6:$B$30,
1+MATCH($H8,$B$6:$B$30,0))
:INDEX($B$6:$B$30, IFERROR(MATCH($H8,$B$6:$B$30,0)
+MATCH("#", IF((INDEX($C$6:$C$30,1+MATCH($H8,$B$6:$B$30,0))
:INDEX($C$6:$C$30,ROWS($B$6:$B$30)))="","#","!"),0),
ROWS($B$6:$B$30))),0),NA()),
MATCH(J$6,$B$6:$E$6,0)), "")

Ответ 10

Вау... Так много решений уже.

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

=INDEX($A$1:$D$9, MATCH($G$3,OFFSET($A$1,MATCH($G$2,$A$1:$A$9,0),0,3,1),0)+MATCH($G$2,$A$1:$A$9,0), MATCH($G$4,$B$1:$D$1,0)+1)

Единственная переменная, которую нужно искать, - это 3, которая представляет собой количество доступных параметров M/N/P, поскольку это повлияет на количество строк. В противном случае решение прекрасно работает во всех возможных сценариях и разных заказах.