Искать в массиве текстовую строку в отдельном массиве, вернуть эту текстовую строку (Excel)

У меня есть столбец текстовых значений (столбец C ниже), они разной длины. У меня есть отдельный массив текстовых значений (столбец A ниже). Мне нужна формула, которая будет просматривать строки в столбце C и возвращать слово из столбца A, которому оно соответствует. Если есть несколько совпадений (редко, вероятно, не произойдет), он вернет только одно.

См. Мой пример:

Столбец D — это результат, который я ищу. Как видите, для этого потребуется нечеткое сопоставление строк. Слово могло появиться в любом месте клетки. Не обязательно учитывать регистр. Но мне нужно, чтобы он вытащил это слово из строки в основном в результате.

Спасибо!


Используйте это:

  = ИНДЕКС (A: A, АГРЕГАТ (15,6, СТРОКА ($ A $ 2: $ A $ 5)/(ISNUMBER (ПОИСК ($ A $ 2: $ A $ 5, C2))  ), 1))  


Еще один, немного более простой:

  = IF (COUNTIF ($ C $ 1: $ C $ 100  , "*" & A2 & "*")> 0, A2, "")  


Вы также можете попробовать эту формулу = INDEX (A: A, SUMPRODUCT (MATCH (1, NOT (ISERR (SEARCH (A: A , C2))) * 1,0))) . Введите его, используя CTRL + SHIFT + ENTER, поскольку это формула массива.

2


Мне кажется, эта формула работает: (Используйте Ctrl + Shift + Enter чтобы формула массива вступила в силу)

{= INDEX ($ A $ 2: $ A $ 5, MATCH (1, COUNTIF (C2, "*" & $ A $ 2: $ A $ 5 & "*"), 0))}

Скриншот листа Excel



Функция ПРОСМОТР

Используйте ПРОСМОТР, одну из функций поиска и ссылки, когда вам нужно посмотреть в в одной строке или столбце и найдите значение из той же позиции во второй строке или столбце.

Например, допустим, вы знаете номер детали для автозапчасти, но не знаете цена. Вы можете использовать функцию ПРОСМОТР, чтобы вернуть цену в ячейку H2, когда вы вводите номер автозапчасти в ячейку H1.

Используйте функцию ПРОСМОТР для поиска одной строки или один столбец. В приведенном выше примере мы ищем цены в столбце D.

Советы: Рассмотрите одну из новых функций поиска, в зависимости от того, какая версию Office, которую вы используете.

  • Используйте ВПР для поиска в одной строке или столбце или для поиска в нескольких строках и столбцы (как таблица). Это значительно улучшенная версия LOOKUP . Посмотрите это видео о том, как использовать VLOOKUP .

  • Если вы используете Microsoft 365, используйте XLOOKUP — это не только быстрее, но и позволяет искать в любом направлении (вверх, вниз, влево, вправо).

Есть два способа использовать ПРОСМОТР: векторная форма и форма массива

  • Векторная форма: используйте эту форму ПРОСМОТРА для поиска одна строка или один столбец для значения. Используйте векторную форму, если вы хотите указать диапазон, содержащий значения, которые вы хотите сопоставить. Например, если вы хотите найти значение в столбце A до строки 6.

  • Форма массива: мы настоятельно рекомендуем использовать VLOOKUP или HLOOKUP вместо форма массива. Посмотрите это видео об использовании ВПР . Форма массива предоставляется для совместимости с другими программами для работы с электронными таблицами, но ее функциональность ограничена.

    Массив — это набор значений в строках и столбцах (например, в таблице), в которых вы хотите выполнить поиск. Например, если вы хотите выполнить поиск в столбцах A и B, до строки 6. ПРОСМОТР вернет ближайшее совпадение. Чтобы использовать форму массива, ваши данные должны быть отсортированы.

Векторная форма

Векторная форма LOOKUP выглядит в диапазоне из одной строки или одного столбца (известного как вектор) для значения и возвращает значение из той же позиции во втором диапазоне из одной строки или одного столбца.

Синтаксис

LOOKUP (lookup_value, lookup_vector, [result_vector])

LOOKUP синтаксис векторной формы функции имеет следующие аргументы:

  • lookup_value Обязательно. Значение, которое LOOKUP ищет в первом векторе. Lookup_value может быть числом, текстом, логическим значением или именем или ссылкой, которая ссылается на значение.

  • lookup_vector Обязательный. Диапазон, содержащий только одну строку или один столбец. Значения в lookup_vector могут быть текстом, числами или логическими значениями.

    Важно: значения в lookup_vector должны быть размещены в порядке возрастания: …, -2, -1, 0, 1, 2, …, AZ, ЛОЖЬ, ИСТИНА; в противном случае LOOKUP может не вернуть правильное значение. Текст в верхнем и нижнем регистре эквивалентен.

  • result_vector Необязательно. Диапазон, содержащий только одну строку или столбец. Аргумент result_vector должен иметь тот же размер, что и lookup_vector . Он должен быть одинакового размера.

Примечания

  • Если функция LOOKUP не может найти lookup_value , функция соответствует наибольшему значению в lookup_vector , который меньше или равен lookup_value .

  • Если lookup_value меньше наименьшего значения в lookup_vector , ПРОСМОТР возвращает значение ошибки # Н/Д.

Примеры векторных изображений

Вы можете опробовать эти примеры на своей собственной таблице Excel, чтобы узнать, как работает функция ПРОСМОТР. В первом примере вы получите электронную таблицу, похожую на эту:

  1. Скопируйте данные из следующей таблицы и вставьте их в новый рабочий лист Excel.

    Скопируйте эти данные в столбец A

    Скопируйте эти данные в столбец B

    Частота

    4,14

    Цвет

    красный

    4,19

    оранжевый

    5,17

    желтый

    5,77

    зеленый

    6.39

    синий

  2. Затем скопируйте формулы LOOKUP из следующей таблицы в столбец D вашего рабочего листа.

    Скопируйте эту формулу в столбец D

    Вот что делает эта формула

    Вот результат, который вы увидите

    Формула

    = LOOKUP (4.19, A2: A6, B2: B6)

    Ищет 4.19 в столбце A и возвращает значение из столбца B, которое находится в тот же ряд.

    оранжевый

    = ПРОСМОТР (5,75; A2: A6; B2: B6)

    Выполняет поиск 5,75 в столбце A, сопоставляет ближайшее меньшее значение (5,17) и возвращает значение из столбца B, который находится в той же строке.

    желтый

    = LOOKUP (7.66, A2: A6, B2: B6)

    Ищет 7.66 в столбце A, соответствует ближайшему меньшему значению (6.39) и возвращает значение из столбца B, находящегося в той же строке.

    blue

    = LOOKUP (0, A2: A6, B2: B6)

    Выглядит вверх 0 в столбце A и возвращает ошибку, поскольку 0 меньше наименьшего значения (4,14) в столбце A.

    # Н/Д

  3. Чтобы эти формулы отображали результаты, вам может потребоваться выбрать их в свой лист Excel, нажмите F2, а затем нажмите Enter. При необходимости отрегулируйте ширину столбца, чтобы увидеть все данные..

Форма массива

Совет. Мы настоятельно рекомендуем использовать VLOOKUP или HLOOKUP вместо формы массива. Посмотрите это видео о ВПР; в нем приведены примеры . Форма массива LOOKUP предусмотрена для совместимости с другими программами для работы с электронными таблицами, но ее функциональность ограничена.

Форма массива LOOKUP ищет в первой строке или столбце массива указанное значение и возвращает значение из той же позиции в последняя строка или столбец массива. Используйте эту форму ПРОСМОТР , когда значения, которые вы хотите сопоставить, находятся в первой строке или столбце массива.

Синтаксис

LOOKUP (lookup_value, массив)

LOOKUP Синтаксис формы массива функции имеет следующие аргументы:

  • lookup_value Обязательно. Значение, которое LOOKUP ищет в массиве. Аргумент lookup_value может быть числом, текстом, логическим значением или именем или ссылкой, которая ссылается на значение.

    • Если LOOKUP не может найти значение lookup_value , он использует наибольшее значение в массиве, которое меньше или равно lookup_value .

    • Если значение lookup_value меньше наименьшего значения в первой строке или столбце (в зависимости от массива размеры), ПРОСМОТР возвращает значение ошибки # Н/Д.

  • массив Обязательно. Диапазон ячеек, содержащий текст, числа или логические значения, которые вы хотите сравнить с lookup_value.

    Форма массива ПРОСМОТР очень похожа на функции HLOOKUP и VLOOKUP . . Разница в том, что HLOOKUP ищет значение lookup_value в первой строке, VLOOKUP выполняет поиск в первом столбце, а LOOKUP выполняет поиск в соответствии с размерами массива.

    • Если массив покрывает область, которая шире, чем высота (столбцов больше, чем строк), LOOKUP выполняет поиск значения lookup_value в первой строке.

    • Если массив квадратный или выше своей ширины (строк больше, чем столбцов), ПРОСМОТР поиск в первом столбце.

    • С помощью функций HLOOKUP и VLOOKUP , вы можете индексировать вниз или поперек, но LOOKUP всегда выбирает последнее значение в строке или столбце.

      Важно : Значения в массиве должны быть расположены в порядке возрастания: …, -2, -1, 0, 1, 2, …, AZ, FALSE, TRUE; в противном случае LOOKUP может не вернуть правильное значение. Текст в верхнем и нижнем регистре эквивалентен.

Оцените статью
clickpad.ru
Добавить комментарий