Quote:
Originally Posted by Max
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.