 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Links / March 2007Matching Criteria in adjacent columns
Here's this one: I want to have a formula that will return the value of a cell that is adjacent to specific criteria in the adjacent cells next to it. For example: Column A Column B Column C Column D Column E Location 1 Product 1 Delvery 1 Return 1 Cost 1 Location 2 Product 3 Delvery 3 Return 2 Cost 3 Location 3 Product 5 Delvery 4 Return 1 Cost 1 Location 2 Product 2 Delvery 1 Return 1 Cost 3 Location 1 Product 1 Delvery 3 Return 2 Cost 4 Location 1 Product 1 Delvery 1 Return 1 Cost 3 So, in essence, I would like the formula to be along the lines of if all the right values are in the same row, return the value of the last cell in that row to a specific location. Something like: If "Location 1" is in column one and is adjacent to "Product 1" in the next and "Delivery 1" is next to that and "Return 2" is next to that, then give me the value in the next cell in Column E "Cost ?" I hope this can be done. I've been playing around with SUM and SUMIF, and VLOOKUP and SUMPRODUCT, and I've gotten close but not close enough. Any/all help will be greatly appreciated.  Signaturefrankjh19701 Frank, didn't we cover this comprehensively in your earlier thread in .worksheet.functions, re: http://tinyurl.com/ytkp2b where suggested expression was, array-entered (press CTRL+SHIFT+ENTER): =INDEX($E$2:$E$100,MATCH(1,($A$2:$A$100=G1)*($B$2:$B$100=H1)*($C$2:$C$100=I1)*($D$2:$D$100=J1),0)) There was also an illustrative sample provided for your reference: http://www.savefile.com/files/555218 Matching multiple criteria.xls SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > > Here's this one: [quoted text clipped - 26 lines] > and VLOOKUP and SUMPRODUCT, and I've gotten close but not close enough. > Any/all help will be greatly appreciated. Max;467581 Wrote: > Frank, didn't we cover this comprehensively in your earlier thread in > ..worksheet.functions, [quoted text clipped - 50 lines] > enough. > > Any/all help will be greatly appreciated. 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. Signaturefrankjh19701 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. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- "frankjh19701" <frankjh19701.448b91@excelbanter.com> 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 [quoted text clipped - 8 lines] > 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. Max;468465 Wrote: > 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 .. [quoted text clipped - 38 lines] > > 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. Signaturefrankjh19701 > .. If I had the ability to show you my spreadsheet, > it would make things easier, Yes, definitely. Perhaps you could use either of the 2 free filehosts listed below to upload your sample (desensitized as necessary) and then post the link to it in response here (the link is generated when you upload, just copy and paste it here) http://www.flypicture.com/ http://cjoint.com/index.php For cjoint.com (it's in French), just click the "Browse" button, navigate to folder > select the file > Open, then click the button centred in the page below (labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your response here. .. alternatively, you could send your sample over to me at: demechanik@yahoo.com I'd prefer the former, though, which keeps discussions visible here for the mutual benefit of all. But whichever track you decide to take, pl post a reply here to let me know .. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > In my original 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. [quoted text clipped - 14 lines] > the ability to show you my spreadsheet, it would make things easier, I > think. Please let me know if this explanation helps at all. Max;469076 Wrote: > > .. If I had the ability to show you my spreadsheet,- > > it would make things easier,- [quoted text clipped - 61 lines] > I > > think. Please let me know if this explanation helps at all. - http://www.flypicture.com/download/MTQzNjE2 If I did it correctly, this is the link. If it's not, et me know and I'll try it again. Signaturefrankjh19701 > If I did it correctly, this is the link. Yes, you did. In your sample, could you illustrate exactly where your inputs will be and the expected formula returns based on the inputs? Save and re-upload the sample, then post the new link to it here. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- demechanik@yahoo.com;469786 Wrote: > > If I did it correctly, this is the link. > [quoted text clipped - 7 lines] > xdemechanik > --- O.K. try this one. I've revised the sheet to include as much detail as I thought of. If there is anything else, please let me know. Signaturefrankjh19701 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.