Returning all values

[okanem]

I have a problem I know someone has an easy answer too.

Sheet 1 contains the following

A1 : A6 all have ref1 entered in them
A7 : A12 all have ref2 entered in them

B1 : B6 list ref1 unique id's 60001 to 60006
B7 : B12 list ref2 unique id's 70001 to 70006

On sheet 2 I want to be able to type in either ref1 or ref2 into Cell
A1 making the entire list of unique id's relating to that ref appear in
B1 to B whatever it takes to list them all.

There are a lot of ref's and multiple unique ID's per ref.

Tried using the vlookup but only the first unique ref relating to the
ref I entered gets returned.

Hope someone can help

Eternally grateful
Okanem

 

[robert111]

first thought is, why not use a pivot table?

When you reply we will be better able to help

robert111

 

[Max]

One play ..

In Sheet2, A1 will house, eg: ref1

Put in B1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(Sheet1!B:B,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Put in C1:
=IF(Sheet1!A1="","",IF(Sheet1!A1=$A$1,ROW(),""))

Select B1:C1, copy down to say, C20
to cover the max expected extent of returns for the reference in A1

Col B returns the required results, all neatly bunched at the top
(Hide away col C, if desired)

 

[Max]

Select B1:C1, copy down to say, C20

to cover the max expected extent of returns for the reference in A1

The 2nd line above should read as:

to cover the max expected extent of returns for any reference entered in

A1