Vlookup with multiple returns

[Ngwami1]

All,
I have a list of contracts that I would like to bounce of a table that
contains multiple entries of contracts with unique Asset numbers, and return
all incidences of the contract and all its asset numbers.

For Example, in the table below, I need to get all the asset numbers for
contracts
CT70055, CT70090,and CW89011. What syntax should I use?

Contract Sales Rep City Asset Number
CT70045 A. Smits Dallas A0777123
CT70045 A. Smits Dallas A0777124
CT70045 A. Smits Dallas A0777125
CT70045 A. Smits Dallas A0777126
CT70045 A. Smits Dallas A0777127
CT70046 A. Smits Dallas A0777128
CT70046 A. Smits Dallas A0777129
CT70047 J. Rispa Austin A0777130
CT70048 B. Kimerni Dallas A0777131
CT70049 J. Awser Houston A0777132
CT70055 J. Awser Houston A0777133
CT70055 J. Awser Houston A0777134
CT70078 J. Awser Houston A0777135
CT70089 J. Awser Houston A0777136
CT70090 B. Kimerni Waco A0777137
CT70090 B. Kimerni Waco A0777138
CT70090 B. Kimerni Waco A0777139
CT70094 B. Kimerni Waco A0777140
CT70094 B. Kimerni Waco A0777141
CT70094 B. Kimerni Waco A0777142
CW89011 A. Smits Dallas A0777143
CW89011 A. Smits Dallas A0777144
CW89011 A. Smits Dallas A0777145
CW89012 J. Rispa San Antonio A0777146
CW89015 J. Rispa San Antonio A0777147
CW89017 A. Smits Dallas A0777148
CW89021 J. Awser Houston A0777149
CW89021 J. Awser Houston A0777150
CW89021 J. Awser Houston A0777151
CW89021 J. Awser Houston A0777152
CW89021 J. Awser Houston A0777153
CW89021 J. Awser Houston A0777154

 

[Teethless mama]

"Contract" and "Asset_Number" are defined name ranges
Assuming your criterias in
F2:F4

In G2
=IF(ISERR(SMALL(IF(Contract=$F2,ROW(INDIRECT("1:"&ROWS(Contract)))),COLUMNS($A:A))),"",INDEX(Asset_Number,SMALL(IF(Contract=$F2,ROW(INDIRECT("1:"&ROWS(Contract)))),COLUMNS($A:A))))

ctrl+shift+enter, not just enter
copy across and down as far as needed

 

[Max]

One way is to use autofilter on a helper col
Source table assumed in cols A to D, data from row2 down
List the 3 contracts of interest in say, G2:G4
Then place in E2: =IF(A2="","",COUNTIF($G$2:$G$4,A2))
Copy E2 down to last row of data in col A
Apply autofilter on col E, choose: 1
The filtered lines will be the results that you seek