VLookup has me stumped

[Martin Noordewier]

I wish to be able to type a Last Name (of a person) into cell c5 in Worksheet A and have Excel look up the matching First and Last Names that are listed in Worksheet B in the cell range a3 to a50; so the persons first and last names show back in c5 of Worksheet A. Despite looking at numerous YouTube tutorials I just can't crack this, being an absolute dummy. Thank you.

[#2]

Hi Martin,

VLOOKUP isn't suitable in all cases, in some cases, you may need to use different functions such as INDEX & MATCH combination.

The below example is similar to what you asking for.

But in your real data, you have to make sure that last names in the list are unique.
If there are some duplicates in the list, the formula will return the first matched one and skip the rest!

Please find the attached file

Hope that helps

[#3]

Thanks, Haytham. Much appreciated!

In your example, if the employee list in on the next worksheet in the workbook, how would I configure the Index formula so it looks for the data in that next worksheet.

Cheers!

[#4]

Hi Martin,

Open a new sheet and name it (Employees List), then highlight the entire table in SheetA and press Ctrl+X to cut it out.

Then go to the new sheet and paste it there.

After that, the formula will update automatically as follows:

=INDEX('Employees List'!B2:B6&" "&'Employees List'!C2:C6,MATCH(F5,'Employees List'!C2:C6,0))

Please find this in the attached file

Hope that helps

[#5]

Thanks again, Haytham.

Regards,

Martin.