G

#### Guest

in one cell, kind of like a dependent drop-down list of results, without

sorting the source data located in a different file or using any filters. I

canâ€™t have multiple results spread out over more than 1 cell (because this

will result in many listings with blank cells, thus changing the format of my

sheet) nor do I want to have the results concatenated into 1 long cell.

Background: After combing through many postings in the Excel forums, I

adapted one of the formulas to allow me to find companies that match multiple

criteria on one of my worksheets and then insert the pay rate for these

companies in a separate column on that worksheet based on a pay rate table

located, currently, in a different workbook (a separate Excel file). While

trying to find a solution to this problem, Iâ€™ve created a smaller test

version of the spreadsheet. The formula Iâ€™m using is an array-entered

forumula:

=INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0))

where from the Rates.xls file, column O contains the pay rates to be found

and S contains the Company names to be matched against based on values in my

test sheet. From my test sheet, row 1 has headers in it. Column B is

â€œTypeâ€, column C â€œProgramâ€, column D â€œModelâ€, column E â€œCompanyâ€, and column

G is where I want the rates to appear. In the formula, columns, B, C, & D

are concatenated with /â€™s to match the values found in column K of the Rates

sheet (Example of column K: Networks/Res/Home; following the format

$B8/$C8/$D8).

Now I know that Debra Dalgleish has described how to create dependent

drop-down lists on her site, but in those cases, it requires either having a

sorted list or defining names for each list. Since I have almost 260

companies in the Rates file with other data listed in other columns, I would

rather not sort this list or try and define names for all these companies.

For the most part, the current formula works fine, but 14 of the

companies have more than 1 pay rate available. For these companies, the

formula only finds the first instance. Maybe the solution to this would to

have some sort of combination of this formula and vba or data validation that

would provide a list for these 14 companies. Maybe something that would say,

use the formula, but if any of these 14 companies with their multiple rates

are found, use a data-validated list or vba to display the results in a list.

Would this be possible? All help is greatly apprecitated.