Поиск с учётом регистра

Вы, вероятно, знаете, что для поиска значений в Excel можно комбинировать функции ИНДЕКС и ПОИСКПОЗ или использовать функцию ВПР. Однако такие методы не учитывают регистр. Чтобы выполнить поиск с учётом регистра, используйте функции СОВПАД и ВЫБОР.

Выполнение поиска с учётом регистра с помощью ИНДЕКС и ПОИСКПОЗ
Выполнение поиска с учётом регистра с помощью ВПР

Выполнение поиска с учётом регистра с помощью ИНДЕКС и ПОИСКПОЗ

Чтобы найти оценку учащегося YUKI, как показано на скриншоте выше, используйте функцию СОВПАД для точного сравнения текстовых строк в диапазоне имён учащихся со значением YUKI (из ячейки G5) с учётом регистра каждого символа. Затем объедините функции ИНДЕКС и ПОИСКПОЗ, чтобы получить нужное значение.

Общий синтаксис

=INDEX(return_range,MATCH(TRUE,EXACT(lookup_value,lookup_range),0))

√ Примечание: Это формула массива, для ввода которой необходимо нажать Ctrl+Shift+Enter.

Чтобы узнать оценкуучастника YUKI, скопируйте или введите приведённую ниже формулу в ячейку G6и нажмите Ctrl+Shift+Enter, чтобы получить результат:

=INDEX()D5:D14;MATCH(TRUE;EXACT())«YUKI»;B5:B14);0))

Или используйте Ссылка на ячейку, чтобы сделать формулу динамической:

=INDEX()D5:D14;MATCH(TRUE;EXACT())G5;B5:B14);0))

Пояснение к формуле

=INDEX(D5:D14,MATCH(TRUE,EXACT("YUKI",B5:B14),0))

• EXACT(G5;B5:B14):Функция EXACT сравнивает текстовые строки в диапазоне имён учащихся B5:B14 со значением «YUKI», содержащимся в ячейке G5, и возвращает ИСТИНА, если хотя бы одно значение в диапазоне B5:B14 точно совпадает с YUKI, и ЛОЖЬ — в противном случае. В результате вы получите массив из значений ИСТИНА и ЛОЖЬ следующего вида:
  {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА}
• MATCH(ИСТИНА;)EXACT(G5;B5:B14);0)=MATCH(ИСТИНА;){ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА};0)Аргумент тип_сопоставления, равный 0, указывает функции MATCH найти точное совпадение. В результате функция возвращает позицию искомого значения «ИСТИНА» в массиве — то есть 10, поскольку единственное значение ИСТИНА находится на 10-й позиции.
• INDEX()D5:D14;MATCH(ИСТИНА;)EXACT(G5;B5:B14);0)) = INDEX(D5:D14Функция INDEX возвращает 10-е значение из диапазона оценок D5:D14, то есть A.

Выполнение поиска с учётом регистра с помощью функции ВПР

Чтобы определить страну, из которой JIMMYДля выполнения поиска с учётом регистра вместо функции ВПР следует использовать комбинацию функций ВЫБОР и СОВПАД. Функция СОВПАД сравнит значение «JIMMY» с каждой текстовой строкой в диапазоне имён учащихся с учётом регистра, а затем функция ВЫБОР объединит результат этого сравнения со значениями из диапазона стран. Теперь пришло время продемонстрировать возможности функции ВПР.

Общий синтаксис

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(lookup_value,lookup_range),return_range),2,FALSE)

√ Примечание. Это формула массива, которую необходимо вводить с помощью нажатия Ctrl+Shift+Enter.

Чтобы узнать страну, откуда JIMMYпроисходит, скопируйте или введите приведённую ниже формулу в ячейку G9и нажмите Ctrl+Shift+Enter, чтобы получить результат:

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT()))«JIMMY»;B5:B14);C5:C14);2;FALSE)

Или используйте Ссылка на ячейку, чтобы сделать формулу динамической:

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT()))G8;B5:B14);C5:C14);2;FALSE)

Пояснение к формуле

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(G8,B5:B14),C5:C14),2,FALSE)

• EXACT(G8;B5:B14):Функция EXACT сравнивает текстовые строки в диапазоне имён учащихся B5:B14 со значением в ячейке G8, JIMMY, и возвращает ИСТИНА, если хотя бы одно значение в диапазоне имён учащихся точно совпадает с JIMMY, и ЛОЖЬ — в противном случае. В результате вы получите массив значений ИСТИНА и ЛОЖЬ следующего вида:
  {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}
• CHOOSE({1,2};)EXACT(G8;B5:B14);C5:C14)=CHOOSE({1,2};){ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ};C5:C14):Аргумент номер_индекса функции CHOOSE {1,2}объединяет массив EXACT и значения из C5:C14в двумерный массив следующего вида:
  {ЛОЖЬ;«Китай»;ЛОЖЬ;«Англия»;ЛОЖЬ;«Америка»;ЛОЖЬ;«Индия»;ИСТИНА;«Америка»;ЛОЖЬ;«Индия»;ЛОЖЬ;«Америка»;ЛОЖЬ;«Китай»;ЛОЖЬ;«Англия»;ЛОЖЬ;«Китай»}
• VLOOKUP()ИСТИНА;CHOOSE({1,2};)EXACT(G8;B5:B14);C5:C14);2;ЛОЖЬ) =ИСТИНА{ЛОЖЬ;«Китай»;ЛОЖЬ;«Англия»;ЛОЖЬ;«Америка»;ЛОЖЬ;«Индия»;ИСТИНА;«Америка»;ЛОЖЬ;«Индия»;ЛОЖЬ;«Америка»;ЛОЖЬ;«Китай»;ЛОЖЬ;«Англия»;ЛОЖЬ;«Китай»} Аргумент приблизительный_поиск, установленный в значение ЛОЖЬ, указывает функции VLOOKUP искать точное совпадение значения «ИСТИНА» в первом столбце двумерного массива и возвращать соответствующее значение из 2-го столбца — то есть Америка.

Связанные функции

Связанные формулы