Hi,
I have a table looks like this
------------A----------------------B
_____________________________
1 | apple, orange ------------| aaa
2 | grape, kiwi, strawberry-| bbb
3 | mango, watermelon -----| ccc
4 | cherry ------------------- | ddd
and i would like to get this result
apple-----| aaa
kiwi-------| bbb
grape-----| bbb
mango----| ccc
I have limited knowledge on vlookup, is there anyway to complete it using vlookup?
any help will be grateful.
Last edited by kannoy; 03-04-2015 at 04:04 AM.
can you pls attach sample excel file with desired result
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Hi,
welcome to the Forum.
Assuming your data is in range A1:B4 and your lookup values are in the range D1:D4, then try this......
In E1
and copy down.
Please Login or Register to view this content.
apple, orange a apple a grape, kiwi, strawberry b kiwi b mango, watermelon c grape b cherry d mango c
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Re: Vlookup table array matching with multiple value in single cell separated by comma.
Originally Posted by sktneer
Hi,
welcome to the Forum.
Assuming your data is in range A1:B4 and your lookup values are in the range D1:D4, then try this......
In E1
and copy down.Please Login or Register to view this content.
apple, orange a apple a grape, kiwi, strawberry b kiwi b mango, watermelon c grape b cherry d mango c
Wow. this work like a charm.
Really thank you so much.
You're welcome.
If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Dear,
Put ( apple ) in the cell C1, and the formula in the D1.....try =vlookup("*"&C1&"*";A1:B4;2;0)... and see the result...
Yes, it will work. Why didn't I think of that simple way. lol
Maybe I need a break.
Hi,
There a bug on this formula.
if i want to vlookup
"apple a" but i have an entry pineapple on top of apple, it will match the pineapple first and return me pineapple result instead going down matching apple.
is there any workaround i can fix it?
Thanks.
Dear,
try to attached some file.....with your question...
test.xlsx
Hi, for your reference.
Million Thanks.
Dear,
try to put a space before (apple)..... but this just works if the word (apple) never were the first word.
for exemplo..
grape, apple, strawberry -it works
mango, apple -it works
apple, banana -it doesn´t works - just if there is also a space, this way it works
Sorry, this is all i can do...
Please check this file, hope this works
Try this to see if you get the correct output......
In H2
and copy down.Please Login or Register to view this content.
If Apple comes first and PineApple comes later in column A then your formula will give PineApple result in Both the cases
=VLOOKUP("* "&G2&",*",CHOOSE({1,2},INDEX(" "&$A$2:$A$5&",",0),$B$2:$B$5),2,0)
Try this and copy towards down
Really thank you so much guys.
It's been a very good learning session for me.
Again. million thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks