Forum Discussion

Martin Noordewier's avatar
Martin Noordewier
Copper Contributor
Oct 14, 2018

VLookup has me stumped

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...
excel
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Oct 14, 2018

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

Martin Noordewier's avatar
Martin Noordewier
Copper Contributor
Oct 15, 2018

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!

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Oct 15, 2018

    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

    • Martin Noordewier's avatar
      Martin Noordewier
      Copper Contributor
      Oct 15, 2018

      Thanks again, Haytham.

      Regards,

      Martin.

       

Resources