Using VLOOKUP in a simple context confusion

Solution 1:

Assuming the first table ranges from A1 to C7, and the second from G7 to H7, insert the following formula in C2:

=$A2 & "." & $B2 & "@" & VLOOKUP(LEFT($A2,1) & LEFT($B2,3),$G$2:$H$7,2,FALSE)

enter image description here

Drag the formula down to fill the cells below.

You can also use a combination of LEFT, INDEX and MATCH functions:

=$A2 & "." & $B2 & "@" & INDEX($H$2:$H$7,MATCH(LEFT($A2,1) & LEFT($B2,3),$G$2:$G$7,0))

Drag it down to the other cells. Note if you are using a different regional setting, you need to replace each , with ; on the formula.

Explaination:

LEFT($A2,1) & LEFT($B2,3): finds the inter initial based on the first letter of their first name and the first 3 letters of their last name.
MATCH(LEFT($A2,1) & LEFT($B2,3),$G$2:$G$7,0)): finds the row that relates to the intern's initial.
INDEX($H$2:$H$7,MATCH(LEFT($A2,1) & LEFT($B2,3),$G$2:$G$7,0)): finds the email domain
$A2 & "." & $B2 & "@" & INDEX($H$2:$H$7,MATCH(LEFT($A2,1) & LEFT($B2,3),$G$2:$G$7,0)): builds the email address based on the first name, last name and email domain.

enter image description here