x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 268

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
• 2
1 Solution

Commented:
=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

Commented:
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

Commented:
Coming back, Kris?
0

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.