Необходимо создать формулу в таблице Excel

Необходимо привязать формулу к выпадающему списку. 
См. таблицу во вложении

Задача 1:
т.е. есть список поставщиков (см. в файле страницу "Лист1" (их там 3 типа, 3 типа цен)), к каждой группе присвоена своя стоимость.

НЕОБХОДИМО, чтобы при выборе поставщика из выпадающего списка (см. файл на странице "Лист2") в определенной ячейке появлялась соответствующая, данному поставщику сумма. 

P.S.: Важно, чтобы при наборе названия поставщика (там где выпадает данная ячейка) при наборе первых букв – автоматом предагалось подставить необходимого поставщика (чтобы не мотать и не искать его по списку) 
 
и второй момент самый важный, данные колонок КЛИЕНТ "C" и ПОСТАВЩИК "D"  заполняются путем копирования данных из нашей СРМ и просто вставляются (в колонке ПОСТАВЩИК-название поставщиков 100% соответствуют названиям скопированным из СРМ) здесь необходимо создать правило проверки данной колонки D т.е. при вставки если название вставляемого соответствует названию которое есть в выпадающем списке – то сумма, которая ему соответствует должна заполняться автоматом. Сейчас при  копировании/вставке ячейка затирается, и пропадает выпадающий список.

Предлагайте свою цену за работу