ВПР по нескольким столбцам в двух книгах

Привет, я знаю, что это старая тема, но я только что обнаружил переполнение стека,

Следующая формула будет выполнять поиск на основе объединенных значений без необходимости добавления дополнительного составного поля.

=IFERROR(INDEX(Sheet1!C$2:C$7,MATCH((A2&B2),(Sheet1!A$2:A$7&Sheet1!B$2:B$7),0),0),0)

**NB: это формула массива, поэтому не забудьте использовать Ctrl-Shift-Enter, чтобы заставить ее работать :)

так что вы можете видеть, на что ссылается каждая часть (извините, я использовал свои собственные фиктивные данные), вот две таблицы данных

Приведенная выше формула используется в ячейке C2 листа 2 и ищет значения Sheet2!A2 и B2 в столбцах A и B Sheet1.

Выяснение этой формулы сэкономило мне дни работы, надеюсь, это поможет :)

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

test1.xlsx, книга с именем, фамилией, номером телефона и без адреса электронной почты

 

test2.xlsx, рабочая книга с именем, фамилией и адресом электронной почты

 

Поэтому мне нужно заполнить адреса электронной почты для каждой записи из test2.xlsx в test1.xlsx, проверив совпадение имен с помощью ВПР.

 

Шаг 1.) Мне нужно объединить поля first_name и last_name и сохранить значения в одной ячейке.

 

Сделать это:

а. Создайте новый столбец в каждой книге. Я назвал его full_name

 

 

b. In the new column in each workbook, create the forumla =(A2&B2). This will join the two values together. Like so:

 

 

c. Then select all the fields in that new full_name column and copy then paste special > values (to get rid of the formula)

 

 

Step 2.) Now that we have our new columns with the data we need to lookup in each workbook, we're going to create a new range in the workbook containing the email address (test2.xlsx). So highlight the two columns full_name and email_address and define a name.

 

 

Step 3.) Now return to your test1.xlsx workbook (the one that does not have the email addresses populated) and in the empty email_address cell (we'll start with E2), write the following formula in the formula bar:

=VLOOKUP(D2,test2.xlsx!emailinfo,2,FALSE)

Имея в виду, что синтаксис функции ВПР таков:

= VLOOKUP ( lookup_value , table_array , col_index_num , range_lookup )

 

 

Step 4.) Now let's say someone didn't have an email address, say Tom Jones. We're going to get a nasty looking #N/A value in our cell:

 

Чтобы избавиться от этого:

a. Select all the column headers by clicking the 1

 

 

b. Click Data > Filter icon. Uncheck (Select All) and check ONLY #N/A.

 

 

c. Click OK. Back to the work book, simply highlight the email field containing the value #N/A and delete.

До:

После:

 

 

d. Either turn off your filter, or go back into filter settings and recheck (Select All) and now instead of nasty looking #N/As all over the place you'll have clean looking empty spaces.

(Очевидно, что с такими маленькими таблицами вышеизложенное кажется немного непрактичным, но НАСТОЯЩИЕ книги, с которыми я работаю, содержат тысячи записей, поэтому эти приемы оказались очень кстати)

Надеюсь, это поможет другим в будущем! Пожалуйста, оставляйте любые комментарии или альтернативы или предложения, обратная связь всегда приветствуется! :)

не видя данных, мой лучший вопрос - вам нужно «ЛОЖЬ» в качестве последнего критерия в VLookup, а не «истина», поскольку вы хотите точного совпадения.

http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

Кроме того: не могли бы вы использовать оценку формулы трассировки, чтобы точно сказать нам, какая часть форума приводит к ответу «ЛОЖЬ».