If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Matching 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. -- frankjh19701 |
#2
|
|||
|
|||
Matching Criteria in adjacent columns
Frank, didn't we cover this comprehensively in your earlier thread in
..worksheet.functions, 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=IĀ*1)*($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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "frankjh19701" wrote: 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. -- frankjh19701 |
#3
|
|||
|
|||
Matching Criteria in adjacent columns
Max;467581 Wrote: Frank, didn't we cover this comprehensively in your earlier thread in ..worksheet.functions, 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=IĀ*1)*($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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "frankjh19701" wrote:- 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. -- frankjh19701 - 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. -- frankjh19701 |
#4
|
|||
|
|||
Matching Criteria in adjacent columns
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. |
#5
|
|||
|
|||
Matching Criteria in adjacent columns
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 .. 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. -- frankjh19701 |
#6
|
|||
|
|||
Matching Criteria in adjacent columns
.. 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: 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 ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "frankjh19701" wrote in message ... 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. 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. |
Thread Tools | |
Display Modes | |
|
|