Lookup formula help??

sezuh · Jul 20, 2011

Hi,
in column "F" i have list In column "G" some numbers has been assigned to each row what i need lookup formula or any exel formula for that matter ,if
i put the number in column g to give me a result of the string of numbers fromcolumn "F".
here is an example;

Excel Workbook

F

G

H

I

J

K

1

list

num.

lookup result

2

1 2 3

1

1 2 3,4 5 6

3

1 4 7

2

1 4 7,2 5 8

4

1 5 9

3

1 5 9,2 6 7

5

1 6 8

4

1 6 8,3 5 7

6

2 4 9

7

2 5 8

2

8

2 6 7

3

9

3 4 8

10

3 5 7

4

11

3 6 9

12

4 5 6

1

13

7 8 9

Sheet2

thanks in advance for any help,appreciate very much.

Domenic · Jul 20, 2011

First, place the following code in a regular module (Alt+F11 > Insert > Module > Copy/Paste > Alt+Q)...

Code:

[font=Verdana][color=darkblue]Function[/color] AConcat(a [color=darkblue]As[/color] [color=darkblue]Variant[/color], [color=darkblue]Optional[/color] Sep [color=darkblue]As[/color] [color=darkblue]String[/color] = "") [color=darkblue]As[/color] [color=darkblue]String[/color]
[color=green]' Harlan Grove, Mar 2002[/color]

    [color=darkblue]Dim[/color] Y [color=darkblue]As[/color] [color=darkblue]Variant[/color]

    [color=darkblue]If[/color] [color=darkblue]TypeOf[/color] a [color=darkblue]Is[/color] Range [color=darkblue]Then[/color]
    
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Y [color=darkblue]In[/color] a.Cells
            AConcat = AConcat & Y.Value & Sep
        [color=darkblue]Next[/color] Y
    
    [color=darkblue]ElseIf[/color] IsArray(a) [color=darkblue]Then[/color]
    
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Y [color=darkblue]In[/color] a
            AConcat = AConcat & Y & Sep
        [color=darkblue]Next[/color] Y
        
    [color=darkblue]Else[/color]
    
        AConcat = AConcat & a & Sep
    
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
[color=darkblue]End[/color] [color=darkblue]Function[/color]
[/font]

Then try the following...

K2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUBSTITUTE(AConcat(IF($G$2:$G$13=J2,","&$F$2:$F$13,"")),",","",1)

Lookup formula help??

sezuh

Well-known Member

Excel Facts

Domenic

MrExcel MVP

Similar threads

Forum statistics

Share this page

Lookup formula help??

sezuh

Well-known Member

Excel Facts

Domenic

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock