Multi-lookup formula

[jwpickett]

I have a situation where I cannot find a good formula to use when I need to return multiple values from an array. It works fine for single values. I'm having to concatenate values to get the desired results. Let me explain.

record A B C D E F G H I J DESIRED RESULT
1 1 A
2 1 B
3 1 C
4 1 D
5 1 E
6 1 1 1 F,G,H
7 1 1 H,J
8 1 G,I
9 1 1 1 1 1 G.H.I.J,K
10 1 1 1 F,I,J


Where columns headed A-D can only have one selection and E-J can have multiple selections. I've tried the LOOKUP function which works where there is only one selection. When applied to record 6, for example, it only returns the first instance of finding the "1", which is column E, ignoring the info in G, H and I.

anyone????

thanks

 

[jwpickett]

Let me re-do this. It didn't come out like it was intended. My bad.

 

[jwpickett]

sda14 data.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	record	A	B	C	D	E	F	G	H	I	J	DESIREDRESULT
2	1	1										A
3	2		1									B
4	3			1								C
5	4				1							D
6	5					1						E
7	6						1	1	1			F,G,H
8	7							1		1		H,J
9	8							1				G,I
10	9						1	1	1	1	1	G.H.I.J,K
11	10						1		1		1	F,I,J
12
13	WherecolumnsheadedA-EcanonlyhaveoneselectionandF-Jcanhavemultiple.
Sheet2

 

[Paddy1979]

Some one smarter than me may know a standard function , but my solution would be to create a UDF

Function MLookup(rng As Range, row As Long)

' row is the row number of the values to return are

Dim cell As Range, st As String

   Application.Volatile
   st = ""

For Each cell In rng
   If cell.Value<> "" Then
      st = st & Cells(row, cell.Column).Value & ","
   End If
Next cell
     
MLookup = st

End Function

Book1
	A	B	C	D	E	F	G	H	I
1	Record	a	b	c	d	e	f	g
2	1	3	3						a,b,
3	2	3	3	3	3			22	a,b,c,d,g,
4	3	3			33	3	3	3	a,d,e,f,g,
5	4	3	3						a,b,
6	5		3						b,
7	6		3	34	4		6		b,c,d,f,
8	7				45	56	6	6	d,e,f,g,
Sheet1

 

[Aladin Akyurek]

Control+shift+enter, not just enter...

=SUBSTITUTE(MCONCAT(IF(B2:K2,","&$B$1:$K$1,"")),",","",1)

...and copy down.

MCONCAT requires the free morefunc.xll add-in.

ACONCAT of which you can easiy find the code in VBA here does the same as MCONCAT.

 

[jwpickett]

Thanks. That solved my problem. Kudos to you.