Pull text data using multiple criteria

I have a sheet with names and references and I want to insert the references on a second sheet for the corresponding names. Any ideas how to do this?

Sheet one has column A First Name Column B Surname Column C Reference

On sheet two, I have column A First Name Column B Surname and in Column C, I want to automatically insert the same reference as on sheet one.

Does that make sense?

Try .... in C1 Sheet2



=VLOOKUP($A1&$B1,CHOOSE({1,2},Sheet1!$A$1:$A$100&Sheet1!$B$1:$B$100,Sheet1!$C$1:$C$100),2,0)

Enter with Ctrl+Shift+Enter

That works but it seems only works if the data is in columns A, B and C? In the actual sheet, my data is in columns L, M and N.

I changed the function but if I use L, M and N, I just get #N/A

No worries, I sorted it. Thanks very much, it works fine now.