VBA массив  
http://yandex.kz/sitesearch?text=%D1%81%D0%B2%D0%B5%D1%80%D0%BA%D0%B0+2+%D1%81%D1%82%D0%­BE%D0%BB%D0%B1%D1%86%D0%BE%D0%B2+VBA+%D0%BC%D0%B0%D1%81%D1%8­1%D0%B8%D0%B2&searchid=84804&web=0&lr=164

Вот недалеко одна задача подробнее: http://www.planetaexcel.ru/forum.php?thread_id=37474 Суть такая - одним проходом по массиву из диапазона в словаре запоминаем где что лежит. Затем вторым проходом по массиву из второго диапазона сразу извлекаем нужное в заранее созданный пустой массив (или в этот же, рядом). Выгружаем результат. Думаю секунд 10 на всё нужно...

Hugo,  
 
Боюсь, что "макрос словарь-массивы" в жизни не будет быстрее, чем ПОИСКПОЗ (ВПР) на листе. В данном случае, единственное преимущество макроса - больший контроль над пересчетом со стороны пользователя.  
 
urfin0000,  
 
Сокращенный файл-пример не помешал бы.  
 
Для ускорения формул предлагаю рассмотреть два взаимодополняющих решения:  
 
1) если ВПР используется в нескольких столбцах для вывода результата для отдного и того же значения из нескольких столбцов исходных данных, то ввести дополнительный столбец с функцией ПОИСКПОЗ(), а для вывода результатов использовать ИНДЕКС()  
 
2) сортировка столбца исходных данных, где ищется значение, по возрастанию (или алфавиту) и использовать приблизительный поиск (3-й аргумент 1 или опущен). Первое можно делать макросом, по событию изменения на листе.  
 
3) Поставить формулы на ручной пересчет  
 
4) Если искомое значение может отсутствовать в таблице, во всех формулах с ИНДЕКС делать проверку на равенство искомого найденному в первом столбце исходных данных.

Там я явно избыточные расчеты, т.к., если не ошибаюсь, ПОИСКПОЗ на листе быстрее перебора в VBA, еще даже до использования словаря.    
 
Сейчас понял, почему словарь-массивы будет быстрее :). Даже при моем решении, избыточные расчеты частично присутствуют, т.к. со словарем индексация всех значений произведется за одно прохождение, а с формулами в ячейках за 400.000  
 
Правда с приблизительным поиском кол-во операций не столь ужасающе, т.к. каждая формула сделает не более 20 проверок (а не 400.000 как в точном поиске)

Wow! Спасибо модератору - все сообщения в идеальном порядке и очепятки исправлены :)

В данном случае о такой обработке ошибок не может быть и речи, а на 5 не нужно умножать если использовать вспомогательный столбец с ПОИСКПОЗ. Одно это должно сократить время пересчета в 5 раз, не говоря уже о сортировке+приблизительный поиск, тут экономия должна быть очень серьезной. И еще, если предположить, что формул будет столько же сколько строк в исходных данных, то среднее кол-во проверок на формулу с точным совпадением будет 200.000, а не 400.000.

Други, мож просто огульно принять, что 400000 ВПР не есть хорошо? Все же это прекрасно понимают...  
:-)

{quote}{login=Hugo}{date=12.02.2012 07:36}{thema=}{post}Не понимаю - если формул 400000, то как количество проверок может быть в 2 раза меньше?{/post}{/quote}  
Все от того, что функция будет обрывать поиск после первового попадания в цель, т.ч. если предположить, что искомые значения те же, что и в таблице, то первое найдется с первой проверки, а последнее с 400000-й проверки :)  
 
Только что проверил решение предложенное мной в 400.000 строках с текстовым ключем из 10 символов и возвратом значений из 5-и столбцов. На моем компе ПОЛНЫЙ пересчет занял 5.5 секунды.

Из всего (многого) сказанного мной в этой ветке следует:  
 
1) Hugo прав в том, что для полного пересчета, "Макрос словарь-массивы" будет быстрее ввиду более оптимального алгоритма  
 
2) Формулы поиска (и даже ВПР) могут таки обрабатывать массив из 400.000 строк за вполне приемлимое время, т.е., при желании, без макроса, можно обойтись.

{quote}{login=Hugo}{date=12.02.2012 08:11}{thema=}{post}Ну количество проверок я не считал - просто количество формул, которые будут вынуждены просмотреть весь диапазон.  
А с доп.столбцом интересно получается - я о таком приёме не подумал.{/post}{/quote}  
Ну тут как раз "дьявол в деталях". Твое решение произведет 400.000 проверок с гораздо меньшей скорость на каждую проверку, а мое - 4.000.000 проверок но со значительно большей скоростью, что все же оставит победу за макросом, но позволит формулам избежать разгромного счета :)

Проверил вариант на словаре - примерно 409000 на 403000 строк из примера Кирилла код сверил за 13-15 секунд. Если бы все записи были разными (т.е. словарь был бы полнее) - то вероятно (по логике) работа шла бы чуть дольше (я там просто протянул что было у Кирилла).  
Вообще я думал, что будет быстрее...  
 
 
 
Option Explicit  
 
'Макросом -  
'1.два диапазона в два массива  
'2.создание массива для результатов  
'3.один перебор n значений массива в словарь  
'4.m проверок массива на наличие в словаре и заполнение данными массива результата  
'5.выгрузка результатов (тут нет предварительной очистки диапазона)  
 
Sub compare()  
Dim tm!: tm = Timer  
   Dim a(), b(), c(), iLastrow As Long, i As Long, ii As Long, x As Byte  
 
   '1.  
   With Sheets(1)  
       iLastrow = .Cells(Rows.Count, 1).End(xlUp).Row  
       a = Range(.[f1], .Range("A" & iLastrow)).Value
   End With  
 
   With Sheets(2)  
       iLastrow = .Cells(Rows.Count, 1).End(xlUp).Row  
       b = Range(.[a1], .Range("A" & iLastrow)).Value
   End With  
 
   '2.  
   ReDim c(1 To UBound(b), 1 To 6)  
 
   With CreateObject("Scripting.Dictionary")  
 
       '3.  
       For i = 1 To UBound(a)  
           .Item(a(i, 1)) = i  
       Next  
 
       '4.  
       For i = 1 To UBound(b)  
           If .exists(b(i, 1)) Then  
               ii = .Item(b(i, 1))  
               c(i, 1) = b(i, 1)  
               For x = 2 To 6: c(i, x) = a(ii, x): Next  
           End If  
       Next  
   End With  
 
   '5.  
   With Sheets(3)  
       .[a1].Resize(i - 1, 6) = c
       .Activate  
   End With  
     
Debug.Print Timer - tm  
End Sub

Кирилл, а как насчет:    
=ПРОСМОТР(значение;сортированный_по_убыванию_диапазон;диапазон_результатов)  
Не выиграет у связки ИНДЕКС(...;ПОИСКПОЗ())?

Кирилл, спасибо, понял.  
Не заметил расчеты на листе Sheet2, где используется доп.столбец с ПОИКПОЗ() (невнимательно читал тему)  
Ну а всетаки? Если брать один столбец результатов?