Looking Up Datas when Key Values are Identical

F

Frank

Hi, Does anyone knows a function that I can lookup datas as below

Return data for look up Frank = MEP2-2, IAEMID-248, GABELLI-250, ML-248,
CSFB-248

Return data for look up Sam = ML-2, UBS-4

Frank MEP2 2
Frank IAEMID 248
Frank GABELLI 248
Frank GABELLI 2
Frank ML 248
Frank CSFB 248
SAM ML 2
SAM UBS 4

Thank You!
 
P

Pé

when frank is in cell a1, mep2 is in cell b1 and 2 is in cell c1 use the
following function:

=a1&" = "&b1&"-"&c1

there also is a formula named "concatenate" you can use but this does
exactly the same as using "&"

hope this does it.
 
F

Frank

I have more 20,000 rows of datas. therefore, I need a function that returns
"MEP2-2, IAEMID-248, GABELLI-250, ML-248, CSFB-248" when I look up "Frank". &
and concatenate is too manual.

Thanks.
 
H

Herbert Seidenberg

Assume your data with headers looks like this:
Who Spec Size
Frank MEP2 2
Frank IAEMID 248
Frank GABELLI 248
Frank GABELLI 2
Frank ML 248
Frank CSFB 248
SAM ML 2
SAM UBS 4

Create a Pivot Table..
Layout: Drag Who and Spec into Row, Sum of Size into Data.
Options: Uncheck Grand Totals, AutoFormat.
The PT should look like this:

Who Spec Total
Frank CSFB 248
GABELLI 250
IAEMID 248
MEP2 2
ML 248
SAM ML 2
UBS 4

Notice GABELLI appears only once and has a total of 248+2=250
Pivot Table was used only for this one purpose.

If Who in the PT is located at A20, WhoS is at D20

WhoS Spec_S Cats
Frank CSFB-248, CSFB-248,
Frank GABELLI-250, CSFB-248,GABELLI-250,
Frank IAEMID-248, CSFB-248,GABELLI-250,IAEMID-248,
Frank MEP2-2, CSFB-248,GABELLI-250,IAEMID-248,MEP2-2,
Frank ML-248, CSFB-248,GABELLI-250,IAEMID-248,MEP2-2,ML-248,
SAM ML-2, ML-2,
SAM UBS-4, ML-2,UBS-4,

The formulas for WhoS, Spec_S and Cats are respectively
=IF(A21="",D20,A21)
=B21&"-"&C21&","
=IF(A21="",F20&E21,E21)
Name the columns with the suggested names.

Create and name 2 cells:
Target Result
Frank CSFB-248,GABELLI-250,IAEMID-248,MEP2-2,ML-248,

Enter the name you want to look up into Target
The Result formula is:
=INDEX(Cats,MATCH(Target,WhoS,0)+COUNTIF(WhoS,Target)-1)
 
Top