Quote:
Originally Posted by Max
Here's a revised interp on your orig. post in .worksheet.functions,
and a way to achieve the multiple returns for any one set of inputs ..
Sample construct available at:
http://www.savefile.com/files/565184
Multi returns for matching multi criteria.xls
Assuming source data in cols A to E, from row2 down
Inputs will be entered in say, G2:J2 eg:
Petro, Penn, Transport, 4496
Put in K2:
=IF(ROW(A1)COUNT(L:L),"",INDEX(E:E,SMALL(L:L,ROW( A1))))
Put in L2:
=IF(AND((A2=$G$2)*(B2=$H$2)*(C2=$I$2)*(D2=$J$2)),R OW(),"")
Leave L1 blank
Select K2:L2, copy down to cover the max expected extent of source data.
Hide away col L. Col K will return the required results from col E, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"frankjh19701" wrote
Um, No. Max we didn't. The one you're referring to is the one that I'm
having a problem with. I've copied the formula that you gave me in to a
spreadsheet to try it and it only gave me the first column's answer -
not what the rates were along with it. Finding the the matching
criteria is one thing, finding the minimum rates and returning ALL of
the criteria i.e. Location,Product, Delvery, Return Cost is the total
objective. And honestly, I could see the formula doing only have the
task. If I am wrong, please help me correct this. Or if I'm doing it
wrong, help me through it. Again, the file (thread) you sent me only
does half. I'm grateful for any/all help you can give me on this. I
don't want to sound like I'm ungrateful, far from it, I just want to
understand the concepts here that you are trying to tell me. Thank you. |
In my priginal post I had said that there are multiple choices for each column. You're only showing me how to match one choice from each column. If you need some examples, here's a few I can release:
Column A = Supplier's names such as Petro, Gasol, Fueldem, and Racex. Column B = Terminal's names such as Penn, DellC, SinSpr, Newk. Column C = Carrier's names such as Transport, Sytems, DriverB, LeeLLC. Column D = Distance from Terminals such as 4496, 4500, 3890, 4800. Column E = The Carrier's Rates (Which can depend on mileage from terminal to customer, amount of freight carried (Plus or minus certain weights), or a combination of the two. They can be such as 0.25, 0.12, 0.33, 0.44 -All multiplied by the mileage, or they can be by weight, which is 8000 or <7500, and by commodity shipped(Which I've had on the Y-axis down the first column. So, if you read it from left to right across the worksheet, it could read that you're looking for the lowest cost of a commodity from the shortest distance location, and the least expensive carrier to deliver it. (Maybe even the next cheapest as well). If I had the ability to show you my spreadsheet, it would make things easier, I think. Please let me know if this explanation helps at all.