lookup to bring back multiple values

[zakfalls]

Hi,

I have a sheet with various names in column A with attributes in col B, the
names can occur more than once. a normal vlookup will only bring back the
first value in col B which i know. but i want to bring back all the different
result in column b for a name which occurs more than once.

I know this invloves Arrays, but the eg ive seen is set up in a way where
the name you are looking is on the first line, and amending it does not
really work for me.

Does anyone have any suggestion or a better example of how to do this?

hope ive made this clear!!!

Cheers

 

[Mike H]

Hi,

With your lookup name in E1 try this array formula. Put the formula in a
cell and drag down. When it starts producing #NUM errors then you have found
every occurrence of your lookup value

=INDEX($B$1:$B$1000,LARGE(($A$1:$A$1000=$E$1)*ROW($A$1:$A$1000),COUNTIF($A$1:$A$1000,$E$1)+1-ROW(A1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[zakfalls]

Almost gives me what i need...i can work with this,

Thanks

 

[T. Valko]

Try this...

Data in the range A2:B20

D1 = lookup name
E1 = formula to count how many records meet the condition

=COUNTIF(A2:A20,D1)

Array entered** in D2 and copied down until you get blanks:

=IF(ROWS(D$11)<=E$1,INDEX(B$2:B$20,SMALL(IF(A$2:A$20=D$1,ROW(B$2:B$20)),ROWS(D$22))-ROW(B$2)+1),"")

If your data is sorted so that the names are grouped together it's much
simpler.