Excel Формулы: Как подставить данные из другого файла на основе двух столбцов?

[geronimo]

Есть два эксель-файла. Нужно сравнить:
активный лист, столбец A - второй файл, столбец B;
активный лист, столбец B - второй файл, столбец C.
Если значения совпадают, то нужно скопировать значение из второго файла, из столбца D на активный лист в столбец С.

Я сделал такую формулу:
=ЕСЛИ(И('[таблица2.xlsx]Sheet1'!$B$10=A10);'[таблица2.xlsx]Sheet1'!$C$10=B10);D10;0)

но значения в таблицах идут вразнобой и формула не работает, как надо.

Как составить формулу?

[вложение удалено администратором]

[Администратор]

Если данных мало, то можно попробовать решить вашу задачу с помощью формул.
Формулы вставлены в столбец C.
Используются формулы массива - после записи формулы нажмите Ctrl+Shift+Enter, чтобы вокруг формулы появились фигурные скобки {}.
Сейчас формула просматривает во второй книге строки со 2 по 4.
При работе с формулами массива нельзя указывать большое количество строк, т.к. формулы или перестанут считаться (будут нули) или файл будет медленно работать.
В этом случае потребуется макрос.

В формуле я сделал переносы, чтобы формулу было удобно читать. Переносы можете удалить.

Формула массива для столбца C:

Формула

=ИНДЕКС([Книга2.xlsb]Лист1!$D:$D;
    МАКС(
        ЕСЛИ([Книга2.xlsb]Лист1!$B$2:$B$4=A2;
            ЕСЛИ([Книга2.xlsb]Лист1!$C$2:$C$4=B2;
                СТРОКА([Книга2.xlsb]Лист1!$B$2:$B$4)
            )
        )
    );
1)

[свернуть]

В файле, который я выложил, формулы ссылаются на файл, который находится у меня на рабочем столе.
У вас файл будет в другом месте.
При открытии книги 1, вверху будет жёлтая панель, щёлкните "Включить содержимое". Появится сообщение, что проблемы со связями, выберите "Изменить связи..." и выберите книгу 2.

[вложение удалено администратором]

[Администратор]

Другой способ. Для этого способа, второй файл должен быть открыт. После закрытия второго файла, если открыть первый файл, то формулы возвращают эксель-ошибки.

Если в файле 2, в столбце D могут быть только числа. При этом числа должны распознаваться экселем как числа, а не как текст. Например, если число дробное, оно должно быть с запятой (если у вас российские настройки).

Формула для файла 1, для C2:
=СУММЕСЛИМН([Книга2.xlsb]Лист1!$D:$D;[Книга2.xlsb]Лист1!$B:$B;A2;[Книга2.xlsb]Лист1!$C:$C;B2)


Примечания

1. Формула обычная (не массивная).
2. Можете попробовать указывать столбцы целиком. Если работа с файлом не будет тормозить, то можете так и оставить.



[вложение удалено администратором]

[Администратор]

Другой способ. В этом способе используется допстолбец в файле 2, в столбце E, в котором в E2 такая формула:
=B2&СИМВОЛ(31)&C2

В файле 1 используется формула (не массивная):
=ИНДЕКС([Книга2.xlsb]Лист1!$D:$D;ПОИСКПОЗ(A2&СИМВОЛ(31)&B2;[Книга2.xlsb]Лист1!$E:$E;0);1)


Примечания

1. Используется символ 31, т.к. это редкий символ и его не может быть внутри ячейки.
Что это за символ, можете посмотреть в интернете, в таблице "Unicode", под номером 31.

2. Можете попробовать указывать столбцы целиком. Если работа с файлом не будет тормозить, то можете так и оставить.

[вложение удалено администратором]