[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

how to use vlook up for comma separated values

Posted on 2011-03-24
Medium Priority
259 Views
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
Question by:chaitu chaitu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 39

Expert Comment

ID: 35212788
=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

LVL 18

Accepted Solution

krishnakrkc earned 2000 total points
ID: 35212979
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

LVL 39

Expert Comment

ID: 35213008
Coming back, Kris?
0

LVL 12

Expert Comment

ID: 35213044
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses
Course of the Month12 days, 22 hours left to enroll