Поиск адреса ячейки вместо значения

В Excel вы можете искать ячейку и вместо значения ячейки видеть ее адрес. Допустим, у вас есть соответствующее значение в ячейке B12. С помощью формулы вы можете получить адрес B12, а не его значение.

В этом уроке будут рассмотрены возможные способы написания формулы для этого.

Получить адрес ячейки вместо значения (ЯЧЕЙКА + ИНДЕКС + ПОИСКПОЗ)

Чтобы написать эту формулу, выполните следующие действия:

Сначала введите функцию ЯЧЕЙКА в ячейку и в первом аргументе укажите «адрес» в качестве типа_информации.
После этого во второй аргумент введите функцию ИНДЕКС.
Затем в аргументе массива INDEX укажите имена, которые есть в столбце A. Диапазон будет A2:A1001.
Теперь во втором аргументе ИНДЕКС введите функцию ПОИСКПОЗ.
Далее в команде ПОИСКПОЗ укажите искомое значение в аргументе искомое_значение и снова укажите диапазон A2:A1001 в аргументе искомый_массив. И используйте 0 в аргументе [match_type], чтобы выполнить точное совпадение.
В конце введите закрывающие скобки и нажмите Enter, чтобы получить результат.

Когда вы нажмете Enter, он вернет адрес ячейки искомого значения.

Имя «Алек Райт» находится в ячейке A5, и именно это вы получите в результате.

 =CELL("address",INDEX(A2:A1001,MATCH(C1,A2:A1001,0)))

Как работает эта формула?

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

В первой части у нас есть функция ПОИСКПОЗ, которая соответствует искомому значению имени ячейки A2:A1001 и возвращает номер позиции в результате.

первая часть имеет функцию сопоставления

После этого INDEX берет этот номер позиции из MACTH и получает значение диапазона в ^4-й позиции. Это означает ячейку A5.

индекс-занимает позицию после совпадения

SECRET : INDEX возвращает значение четвертой позиции или ячейки A5 диапазона в приведенном выше примере. Но ИНДЕКС как функция возвращает два разных значения. Один — это значение ячейки (которое вы можете увидеть выше), а второй — адрес ячейки, в которой хранится значение (A5). Вы не можете увидеть или получить адрес ячейки как пользователь, но функция CELL достаточно умна, чтобы получить и использовать его.

В конечном итоге функция CELL возвращает адрес ячейки, используя в результате абсолютную ссылку.

Если вы хотите удалить знаки доллара из адреса ячейки, вы можете использовать ЗАМЕНУ.

 =SUBSTITUTE(CELL("address",INDEX(A2:A1001,MATCH(C1,A2:A1001,0))),"$","")

Получить адрес ячейки, используя АДРЕС + ПОИСКПОЗ

Есть еще один способ написать формулу для получения адреса ячейки.

 =ADDRESS(MATCH(C1,A1:A1001,0),1)

Эта формула состоит из двух частей: в первой части у нас есть функция ПОИСКПОЗ, позволяющая получить номер позиции ячейки, в которой находится искомое значение.

совпадение-получает-номер-позиции-ячейки

После этого в функции АДРЕС у вас есть число, возвращаемое функцией ПОИСКПОЗ (в первом аргументе), которое является аргументом СТРОКА для установки номера строки.

А во втором аргументе у нас есть номер столбца.