[ms office - 2019] Excel Help

[rush_tech]

Hi

Could someone lend a hand with excel issue I'm having? I've got 2 lists in a workbook (example attached) what I'm trying to do is match the firstname and lastname columns and add the email address to the list with the form column.

I've been trying to use vlookup, in D2 =VLOOKUP(A2:B2,J2:M3,3,FALSE) which shows a #Value! what am I doing wrong? or is there another way of doing it?

 

Thanks

email_example.xlsx

[TomHD]

Try this in cell D2 then copy down... =VLOOKUP(A2,$J$2:$L$3,3,FALSE)

 

You don't need to reference column M as it is empty

 

So the above formula only works if there are no duplicate first names... have fixed that in the example attached below, let me know if this is what you were after

 

for rush_tech.xlsx

 

When doing VLOOKUP on 2 cells you need a helper column where the 2 values are concatenated and then used to compare to the range in the lookup, hope this explains it well enough!?

Edited January 20, 2020 by TomHD

[rush_tech]

Thanks TomHD that worked , I'd tried it with absolute cell referencing as well

[rush_tech]

I notice that it only checks against first name, is there a way to check both first and last name? I've swapped data around so it checks lastname but there are pupils with the same surname which all get given the same email address

[TomHD]

@rush_tech the example I attached does use both names to do the check?

 

for rush_tech.xlsx

 

 

To expand a little on the solution...

 

=VLOOKUP(A2&B2,$I$2:$L$4,4,FALSE)

 

A2&B2 create the single lookup value by concatenating firstname and lastname, we then create a "Lookup Helper" column in the range we are looking up, that is why we start at I2, as column I is our helper, which is a concatenation of J and K [=CONCATENATE(J2,K2)], the formula then uses the A2&B2 value and looks for it in column I and returns the email value in column L [column 4 of our lookup range] on the same row where I matches A2&B2

 

We enclose the range in $ so that those numbers and letters do not change when copying down

Edited January 20, 2020 by TomHD

[rush_tech]

@TomHD Thanks for your help this I've sorted it now