Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

how to use vlook up for comma separated values

In column comma separted values(with firstname,lastname) are there in other sheet first name and last name in the 2 tabs.

how to use vlookup in this case to find the empid? vlookup.xlsx
0
chaitu chaitu
Asked:
chaitu chaitu
  • 2
1 Solution
 
nutschCommented:
=INDEX($D$2:$D$5,MATCH(1,($F$2:$F$5=RIGHT(A2,LEN(A2)-FIND(",",A2)))*($E$2:$E$5=LEFT(A2,FIND(",",A2)-1)),0))

in cell B2, validated by Ctrl+Shift+Enter (this is an array formula)

Thomas
0
 
krishnakrkcCommented:
Hi,

=INDEX($D$2:$D$5,MATCH(A2,$E$2:$E$5&","&$F$2:$F$5,0))

It's an array formula. Confirmed with CTRL + SHIFT + ENTER

Kris
0
 
nutschCommented:
Coming back, Kris?
0
 
tilsantCommented:
Since empid is going to be a number, you can use (in cell B2 and drag down):
=SUMPRODUCT($D$2:$D$5,--(A2=$E$2:$E$5&","&$F$2:$F$5))
(Not an Array Formula)


Tils.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now