Постановка задачи

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

По ссылке можно скачать сам пример, рассматриваемый в данной заметке.

Логика решения

1. С помощью встроенных средств Excel даем пользователю возможность выбрать нужный элемент из раскрывающегося списка. Можно использовать два варианта: через элемент управления "Поле со списком" и через проверку вводимых значений с помощью списка.
2. Сохраняем номер выбранного элемента (его позицию в списке элементов).
3. Используем полученный номер строки и номер столбца со значением нужного поля для получения его значения.
   Для этого подходит встроенная функция ИНДЕКС:

Из справки:
Функция ИНДЕКС: Возвращает значение элемента таблицы или массива
Синтаксис: ИНДЕКС(массив, номер_строки, [номер_столбца])

В нашем случае в формулу вместо массива будем подставлять название именованного диапазона (для примера "Клиенты"), содержащего данные таблицы-справочника (без шапки).

Создание именованного диапазона:

Excel 2003	Excel 2010

Вариант 1. Пользователь выбирает с помощью элемента управления "Поле со списком"

Данный вариант лучше всего подходит для случаев, когда нужно выбирать один-два раза, точно зная, где будет находиться полученная информация. Он проще, работа ведётся только с одним диапазоном "Клиенты".
Элемент управления служит для выбора и на печать не выводится

Для получения возможности вставлять элементы управления необходимо: в Excel 2003 отобразить панель "Формы", а в Excel 2010 - вкладку "Разработчик".

Вставка элемента управления:

Вариант 2. Пользователь выбирает в ячейках из списка через проверку вводимых значений

Данный вариант лучше всего подходит для случаев, когда нужно выбирать много раз. Использование элементов управления становится трудоёмким.
Выбор производится с помощью проверки вводимых в ячейки значений (тип - "Список").

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

Решением может служить введение дополнительного столбца в таблицу-справочник. Ячейки этого столбца будут содержать наименования элементов справочника вместе с номером строки. В примере я использую формулу вида =СЦЕПИТЬ(СТРОКА(A3)-2;": ";A3).

Этот дополнительный столбец станет новым диапазоном "КлиентыДляВыбора", пользователь получит возможность выбрать по нему, из результата выбора можно получить номер строки, потом по этому номеру - всю остальную информацию из основного диапазона "Клиенты". Это возможно, так как нумерация строк обоих диапазонов совпадает.

Далее, в ячейках, в которых будет производиться выбор из списка, нужно установить проверку данных. Устанавливаем курсор в выбранную ячейку и производим следующие действия:

В выбранном значении находим позицию строки ": ", которая идет после нужного номера (с помощью формулы вида: =НАЙТИ(": ";A4)). После чего получаем сам номер элемента справочника в списке элементов (с помощью формулы вида: =ПСТР(A4;1;B4-1) ).

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