VLOOKUP with 2 columns for both search_key and range-CodePudding

I am struggling to find a way to use a VLOOKUP where the both the search_key and range are 2 columns. In both sheets (seperate workbooks for which I am using IMPORTRANGE) I have 2 columns with First Name and Last Name. The Sheet1 also contains the Hire Date, and I would like to populate the Hire Date from Sheet1 into a column in Sheet2.

I accomplish what I want if I merge the First Name and Last Name into a single column on both sheets. Unfortunately, this cannot be a permanant solution since there are other dependancies where the names must remain seperate columns.

I have created a test worrkbook to showcase the issue. https://docs.google.com/spreadsheets/d/1v3coBJRwJEbrrdgcflV4-dssKgknS-T2werWVjPwxEA

CodePudding user response：

Put this formula in cell Sheet2!C1:

=arrayformula( 
  iferror( 
    vlookup( 
      A1:A & B1:B, 
      { Sheet1!A1:A & Sheet1!B1:B, Sheet1!C1:C }, 
      2, false 
    ) 
  ) 
)

Then format the result column as Format > Number > Date.