Lookup from cells which contains comma-separated array

[MssConfused]

Hi everyone! First post in forum Thanks for your support.

I've a question. For instance

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]apple,lemon,strawberry[/TD]
[TD]fruit[/TD]
[/TR]
[TR]
[TD]green,blue,white,black,grey[/TD]
[TD]color[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I've a list like above. How can we use lookup formulas from comma-separated cells?

white> color
lemon> fruit

 

[Marcelo Branco]

Hi and welcome to Mr Excel

Maybe something like this


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Search​

[/TD]
[TD]

Category​

[/TD]
[TD][/TD]
[TD]

List​

[/TD]
[TD]

Category​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

white​

[/TD]
[TD]

color​

[/TD]
[TD][/TD]
[TD]

apple,lemon,strawberry​

[/TD]
[TD]

fruit​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

lemon​

[/TD]
[TD]

fruit​

[/TD]
[TD][/TD]
[TD]

green,blue,white,black,grey​

[/TD]
[TD]

color​

[/TD]
[/TR]
</TBODY>[/TABLE]


Put the search values in A2, A3...

Array formula in B2 copied down
=INDEX($E$2:$E$10,MATCH("*,"&A2&",*",","&$D$2:$D$10&",",0))

confirmed with Ctrl+Shift+Enter

If you are not familiar with array formulas, take a look at
Array Formulas

Hope this helps

M.

 

[MssConfused]

Wow! Works like a charm. Thank you for all

 

[Marcelo Branco]

Wow! Works like a charm. Thank you for all

Yes, it works, but i overlooked (my bad) that it fails if there are spaces in the list like
apple,lemon ,strawberry
(note the space after lemon)

Try this safer version
=INDEX($E$2:$E$10,MATCH("*,"&A2&",*",","&SUBSTITUTE($D$2:$D$10," ","")&",",0))
Ctrl+Shift+Enter

copy down

M.

 

[MssConfused]

There is no space in my data but thanks this version also for the community.

By the way I've nearly 950K lines. Calculating process takes soo looong. For first 300K it's done in 15 minutes but the results are nice. ^^

Maybe there are more optimized solutions -with or without excel- but I've got the array concept easily thanks to you.

 

[hiker95]

MssConfused,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Maybe there are more optimized solutions

1. Can we have a screenshot of some of your actual raw data?

2. And, can we have a screenshot (manually formatted by you) for the results you are looking for?


To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Marcelo Branco]

There is no space in my data but thanks this version also for the community.

By the way I've nearly 950K lines. Calculating process takes soo looong. For first 300K it's done in 15 minutes but the results are nice. ^^

Maybe there are more optimized solutions -with or without excel- but I've got the array concept easily thanks to you.

Wow..i didn't know that you had 950K rows!
In this case i think a macro is a better solution.

M.

 

[MssConfused]

Hi hiker95,


Thanks for your support. My problem was solved for now. Next time I will try to send like you said.


Hi Marcelo,


Excel stopped responding once time and then I changed the priority of Excel from normal to high as a precation. After 2 hours I've got the results!!


I think I never mess with such a large database in my life lol.

 

[hiker95]

MssConfused,

Hi hiker95, Thanks for your support. My problem was solved for now. Next time I will try to send like you said.

Thanks for the feedback.

You are very welcome. Glad I could help for future threads.

And, come back anytime.