One to many vlookup

[haroldhing]

Hi All

I have two sheets, here is an example of what I am looking at:

Sheet 1:

Scenario: Number 1 Number 2 Number 3
130
10
10
33
etc.

Sheet 2:

Scenario: Number:
1 2067896512
1 2098172651
1 4258712369
10 4258097209
10 5783992762
20 1891237762
33 2349785432
33 2131452332
33 9843527232
130 4354678976
130 7836284285
etc.

I would like to have sheet 1 pull unique numbers from sheet 2 for the
different scenarios. I have been playing with vlookup and I am not sure
if I am using the correct forumla to accomplish this.

Thanks for all your help!

 

[Biff]

Hi!

Assume this is on sheet1, headers in A11:

Scenario: Number 1 Number 2 Number 3
130
10
10
33

This is on sheet2, headers in A1:B1:

Scenario: Number:
1 2067896512
1 2098172651
1 4258712369

etc

Enter this formula on sheet1 in B2:

=IF(COLUMNS($A:A)<=COUNTIF(Sheet2!$A$2:$A$12,$A2),INDEX(Sheet2!$B$2:$B$12,MATCH($A2,Sheet2!$A$2:$A$12,0)+COLUMNS($A:A)-1),"")

Copy across then down as needed. Adjust the ranges to suit.

Biff