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

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

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

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.

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

.. 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.

Max;469076 Wrote:
.. 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. -


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.




--
frankjh19701

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
---

;469786 Wrote:
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
---

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.




--
frankjh19701

"frankjh19701" wrote
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.

http://cjoint.com/?dwq7RxN5Ud

I cannot believe I forgot the link Sorry about that.
Again, thank you for any/all help that you can provide me.

Frank, you forgot to throw in the taps for the kitchen sink bg. I had to
go to excelbanter to pick up your link above (pasted above for the benefit
of other potential responders who might want to plough in here). For some
reason your follow-on posting with the link didn't carry through to google
nor MS' newgroup.

It really looks very tough as-is, with data the way it is and your multiple
intents. Whatever it is, I'll take a closer look, and try to post a positive
closure here for you. Watch this space.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---