Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell (also comma separated)

Michael M

Well-known Member

Joined

Oct 27, 2005

Messages

21,835

Office Version

1. 365
2. 2019
3. 2013
4. 2007

Platform

1. Windows

• Jan 7, 2009

• #2

Hi Feagal and welcome to the Board.

This is ugly but will do what you require, I'm sure there will be a heap of simpler and cleaner answers.
Assuming your lookup table is in A2:B7
and your initial info is in D1

Code:

=VLOOKUP(LEFT(D1,2),A2:B7,2,FALSE)&","&VLOOKUP(MID(D1,4,2),A2:B7,2,FALSE)&","&VLOOKUP(RIGHT(D1,2),A2:B7,2,FALSE)

Regards
Michael M

 

Upvote 0

S

Sal Paradise

Well-known Member

Joined

Oct 23, 2006

Messages

2,457

• Jan 7, 2009

• #3

Excel Workbook
	A	B	C	D	E
1	Lookup	Result	*	Data	Lookup
2	ED	T	*	EH,HU,SE	F,H,E
3	EH	F	*	HU,DR,DR	H,G,G
4	DR	G	*	EH,SE,HU	F,E,H
5	HU	H	*	SE,DR,HU	E,G,H
6	SE	E	*	SE,SE,YU	E,E,E
7	YU	E	*	DR,ED,DR	G,T,G
Sheet1

(If all entries are only 2 letters, use Michael's. If there is a chance that there will be ones with 3, or 5, or 1, or any number other than 2, mine will work)

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,578

Office Version

1. 365

Platform

1. Windows

• Jan 7, 2009

• #4

First, download and install the free add-in, Morefunc. Then define the following...

Insert > Name > Define

Name: Array

Refers to:

=EVAL("{"""&SUBSTITUTE(SUBSTITUTE($D$2," ",""),",",""",""")&"""}")

Click Ok

Then, assuming that A2:B7 contains the source data, try...

=SUBSTITUTE(MCONCAT(IF(ISNUMBER(FIND(","&$A$2:$A$7&",",","&D2&",")),","&$B$2:$B$7,"")),",",1)

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

Upvote 0

F

Feagal

New Member

Joined

Jan 7, 2009

Messages

3

• Jan 8, 2009

• #5

Hi there,


Thank you so much for your help... things are coming together.

However, one thing I forgot to mention - sometimes you won't have 3 seperate bits of data to lookup... sometimes, you will have 1 or 2 or possibly up to 30.

That makes things slightly harder I guess? Something I should have mentioned before I guess.

Any further help in this area would be much appreciated...

Cheers very much - hope you are all enjoying the start of the year so far...

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,578

Office Version

1. 365

Platform

1. Windows

• Jan 8, 2009

• #6

Have you tried the solution I offered?

 

Upvote 0

F

Feagal

New Member

Joined

Jan 7, 2009

Messages

3

• Jan 8, 2009

• #7

Hi Dominec,

I'm having problems getting it to work to be fair - it must be something I'm doing...

Will it cope with different numbers of data within each cell then?

I'll be working on it so will let you know if I manage to get it to work.


Ta,
S

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,578

Office Version

1. 365

Platform

1. Windows

• Jan 8, 2009

• #8

Feagal said:

I'm having problems getting it to work to be fair - it must be something I'm doing...

Click to expand...

Is the formula returning an error value? If so, which one?

Is the formula not returning the proper values? If so, can you provide an example?

Will it cope with different numbers of data within each cell then?

Click to expand...

Yes...

 

Upvote 0

M

mfexcel

Well-known Member

Joined

Jan 8, 2009

Messages

791

• Jan 8, 2009

• #9

Hi Feagal,
Instead of constructing one complicated formula to fit all scenario, may i suggest you break it down into steps and get the thing done?

1) Separate the Data into Columns by using "Text to Columns"
2) Construct Vlookup formula for each "separted" cell (it should be easy)
3) CONCATENATE the results into one cell

Hope this helps.

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,578

Office Version

1. 365

Platform

1. Windows

• Jan 8, 2009

• #10

A few things...

1) I'm not sure why I included the part about defining the name 'Array'. It's completely not necessary.

2) The FIND function is case-sensitive. If you don't want the formula to be case-sensitive, replace FIND with SEARCH.

3) The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

4) I assumed that A2:B7 contains...

ED T
EH F
DR G
HU H
SE E
YU E

5) I assumed that D2 contains...

EH,DR,HU

6) Use the following formula instead, which will allow for inadvertent spaces between the values in D2...

=SUBSTITUTE(MCONCAT(IF(ISNUMBER(FIND(","&$A$2:$A$7&",",","&SUBSTITUTE(D2," ","")&",")),","&$B$2:$B$7,"")),",",1)

Hope this helps!

 

Last edited: Jan 8, 2009

Upvote 0