Учитывая ваши данные выше, я собираюсь сделать несколько предположений. Если все это правда, то HLOOKUP может легко решить вашу проблему. В противном случае может потребоваться более сложная цепочка функций.
Предположения:
- Все соответствующие тексты заголовков в
'Sheet3'!1:1 абсолютно уникальны в этом ряду.
- Все соответствующие тексты заголовков в
'Sheet3'!1:1 имеют формат «[X]: [Y]», где:
- [X] - это значение, которое можно найти в
'SheetA'!W:W
- [Y] - это значение в
'SheetA'!X:X который находится в той же строке, что и [X].
- Все возможные значения в
'SheetA'!CD:CD можно искать в 'Sheet3'!A:A , так что они будут в той же строке, что и данные, предназначенные для извлечения.
- Я просто вставляю это, потому что я заметил, что в Sheet3 есть некоторые повторения этих значений, и хотел убедиться, что нет условий, при которых вы можете искать значение в
'SheetA'!CD:CD который должен был сопоставляться с другим столбцом (и, следовательно, может давать неточные результаты, если сопоставляется в 'Sheet3'!A:A).
- Если упомянутое повторение предназначено исключительно для удобства чтения, могу ли я предложить вместо этого функцию «Стоп-кадр»?
- Все соответствующие значения в
'Sheet3!A:A' являются совершенно последовательными, начиная с "1" в 'Sheet3'!A3 , никогда не повторяется в этом столбце и всегда сортируется по возрастанию.
Учитывая вышеизложенные предположения, я построил свои собственные листы с образцами данных, которые примерно представляют ваш сценарий.
Вот частичный скриншот моего "Sheet3".
И частичный снимок моей "SheetA".
Список аналогов:
- Sheet3
- Мой A:A = Ваш A:A, BY:BY:, CA:CA:, ...
- Мой B:B, C:C, D:D, ... = Ваш B:B, BZ:BZ, CB:CB, ...
- Сита
- Мой A:A = Ваш W:W
- Мой B:B = Ваш X:X
- Мой C:C = Ваш CD:CD
- My D:D = В какой бы столбец вы не хотите поместить найденные данные.
Как вы можете видеть на втором скриншоте, формула для D2:
=HLOOKUP(CONCATENATE(A2,": ",B2),Sheet3!A:Y,C2+2,FALSE)
Пошаговое прохождение формулы:
HLOOKUP позволяет просматривать значение диапазона ячеек по горизонтали, а затем возвращать значение ячейки в том же столбце на основе относительной позиции строки. Он принимает четыре аргумента, три из которых являются обязательными: lookup_value, table_array, row_index_num, [range_lookup]. Это формула, которая будет выполнять всю работу по поиску нужных вам данных в Sheet3 и помещать их в ячейку в SheetA.
- lookup_value - это значение, которое вы хотите, чтобы
HLOOKUP сначала нашел. Это значение должно присутствовать в первой строке table_array, потому что это единственная строка, в которой будет производиться поиск. HLOOKUP также будет возвращать только первое совпадение, поэтому эти значения также должны быть уникальными. Здесь мы используем CONCATENATE для построения строки поиска.
- CONCATENATE позволяет объединять различные строки и значения в одну строку. Он принимает ряд аргументов, упорядоченных в соответствии с последовательностью их размещения в результирующей строке.
- A2 - наш первый аргумент для
CONCATENATE . Первой частью нашей строки будет "Имя образца" в A2.
- ":" - это наш второй аргумент для
CONCATENATE . Это помещает двоеточие и пробел в строку, чтобы соответствовать формату значений в 'Sheet3'!1:1 .
- B2 - наш последний аргумент для
CONCATENATE . Он извлекает "Sample ID" из B2, чтобы завершить синтаксис, используемый для заголовков в 'Sheet3'!1:1 .
- table_array - это ссылка на диапазон ячеек, с которыми вы хотите работать с
HLOOKUP . Помните, что первая строка должна содержать строку lookup_value . Этот диапазон также должен охватывать все возможные значения для row_index_num .
- Sheet3!A:Y - это ссылка на все ячейки в столбцах с A по Y (единственные, заполненные в моем Листе 3), Листа 3. Это гарантирует, что любые данные, добавленные позже в новых строках, также попадут в область поиска. Если бы данные собирались добавлять в новые столбцы вместо строк, я бы хотел использовать ссылку
Sheet3!1:32 (в настоящее время 32 - это последняя строка в моем Листе3). Если бы данные могли быть добавлены новыми столбцами и новыми строками, я бы сослался на весь лист с Sheet3!1:1048576 или Sheet3!A:XFD . (Примечание. Ссылка "Весь лист" действительна для Excel 2013. Более ранние версии могут иметь меньшие ограничения на строки / столбцы - отрегулируйте 1048576 или XFD в зависимости от ситуации.)
- row_index_num - положительное целое число, представляющее позицию строки относительно самой верхней строки в table_array . Это сообщает
HLOOKUP какую ячейку вы хотите вернуть из соответствующего столбца. Обратите внимание, что, поскольку HLOOKUP выполняет поиск только для lookup_value в верхней строке table_array, а row_index_num не может быть отрицательным, вы не можете использовать HLOOKUP (по крайней мере, сам по себе) для возврата информации из ячеек, которые находятся выше lookup_value .
- C2+2 - Поскольку все значения в
'Sheet3'!A:A являются совершенно последовательными, без пропущенных целых чисел, и всегда будут упорядочены по возрастанию, мы можем использовать эти значения (также представлены в 'SheetA'!C:C) в качестве индикаторов номеров строк для данных, которые мы хотим найти. +2 здесь, чтобы учесть тот факт, что нумерация начинается с "1" в строке 3 листа 3.
- [range_lookup] - необязательный аргумент для
HLOOKUP . Возможные значения: ИСТИНА или ЛОЖЬ, чтобы указать, хотите ли вы, чтобы приблизительные совпадения были действительными или разрешены только точные совпадения. Excel по умолчанию имеет значение TRUE (приблизительное соответствие), если этот аргумент пропущен, что часто может привести к нежелательному поведению, особенно если ваш лист не сортируется определенным образом. Таким образом, мы указываем FALSE здесь, чтобы HLOOKUP только подбирал точное совпадение.
Адаптируя вышеизложенное к макету вашего листа, я полагаю, что эта формула вам понадобится для ячейки 'SheetA'!CE42 (если вы хотите, чтобы данные были сброшены):
=HLOOKUP(CONCATENATE(W42,": ",X42),Sheet3!A:CB,CD42+2,FALSE)
Обратите внимание, что если ваши данные в Sheet3 идут дальше вправо, чем столбец CB, и / или данные могут быть добавлены в другие столбцы, вам нужно соответствующим образом настроить table_array .
