VLOOKUP и IF в нескольких листах Excel

Question

У меня есть два листа Excel, как показано ниже, в рабочей тетради

Лист 1 и 2:

Ниже приведено соотношение полей между двумя листами

• EID такой же, как EmID
• DepID такой же, как DependentID
• DepDOB такой же, как DependentDOB

Я хотел бы сравнить EID с EmID. Если совпадают, сделайте еще одно сравнение по DepID с DependentID. Если DepID пуст, то сравните DepDOB с DependentDOB. Когда все это верно, получите DependentFirstName и DependentLastName из Sheet2 и скопируйте его в новый столбец на листе 1.

Пожалуйста, смотрите изображение ниже для моего предполагаемого результата.

Выход:

Пожалуйста, посоветуйте метод для достижения этой цели. Если VLOOKUP и IF должны быть объединены, пожалуйста, дайте мне знать формулу. Это дает мне кошмары :(

Answer 1

Решение по запросу

Простой способ сделать это так, как вы просили - использовать вспомогательные столбцы. Допустим, лист 1 выглядит как ваш выходной пример с показанными столбцами A:F, а лист 2 также показывает A:F. Скажем, мы используем столбец G для вспомогательного столбца на каждом листе.

Вспомогательный столбец объединяет три значения сравнения. Таким образом, на каждом листе G2 будет содержать:

=A2&E2&F2

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

VLOOKUP требует, чтобы столбец поиска был самым левым в массиве. Вы можете выполнить тот же стиль поиска, используя INDEX плюс MATCH, который не имеет этого ограничения. Лист 1, C2 будет содержать:

=INDEX(sheet2!B:C,MATCH(G2,sheet2!G:G,0),1)

и в D2:

=INDEX(sheet2!B:C,MATCH(G2,sheet2!G:G,0),2)

Скопируйте их вниз по столбцу. MATCH находит ключ, соответствующий строке на листе 2, и возвращает соответствующие имена.

Вы можете скрыть вспомогательные столбцы, если не хотите их видеть. Если вам интересно, почему ключи, связанные с DepDOB, не похожи на дату, это потому, что она использует внутреннее представление, которое Excel использует для хранения даты.

Более простое решение

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

=LEFT(B2,FIND(" ",B2)-1)

и D2 будет:

=RIGHT(B2,LEN(B2)-FIND(" ",B2))