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