| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with multiple returns
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup with multiple returns
"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_N umber,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 "Ngwami1" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup with multiple returns
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ngwami1" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP Returns a zero value | Excel Discussion (Misc queries) | |||
| VLOOKUP returns #REF!? | Excel Discussion (Misc queries) | |||
| VLOOKUP Returns #REF | Excel Worksheet Functions | |||
| VLookup returns #VALUE! | Excel Worksheet Functions | |||
| Vlookup Multiple Returns #REF | Excel Discussion (Misc queries) | |||
Linear Mode
