Sorting and Matching criteria across rows to return a result in the last cell

COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E
Petro Penn Transport 4496 0.25

I would like a formula that will find for every time there is "Petro"
in Column A, and "Penn" in Column B, and "Transport" in Column C, and
"4496" in Column D give me the vaule of Column E.

Note: There are about 150 different choice for Column A. There are
about 70 different choices for Column B. , about 10 different choices
for Column C. , about 200 choices for Column D, and infinite choices
for Column E.

Any and all help would be greatly appreciated.

--
frankjh19701

Assuming source data as posted, within rows 2 to 100

Inputs entered in say, G1:K1 are: Petro, Penn, Transport, 4496

Put in L1, array-enter (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))

L1 should return the required result from col E. If you have other sets of
inputs entered in G2:K2, G3:K3, etc, just copy L1 down to return
correspondingly.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"frankjh19701" <frankjh19...@excelbanter.com> wrote in message
news:frankjh19...@excelbanter.com...

should read as:
> Inputs entered in say, G1:J1 ...
> ... inputs entered in G2:J2, G3:KJ3, etc, ...

Is there any way you can walk me through this formula? I've been trying
it every which way I could and I'm going nowhere. I entered it exactly
like you said, and all I get is Value#. If I'm looking for one
particular value in column A, that is also adjacent to a particular
value in column B, and so on until column D, I want to return the value
that is adjacent to them all in the next column, in this case column E.
I just don't see where your formula can do that. Please help me.

--
frankjh19701

You probably didn't **array-enter** it properly as per my earlier step:

That's why you got the VALUE error. Try it again. With the formula already
pasted into the formula bar, click inside the formula bar, then press
CTRL+SHIFT+ENTER (instead of just pressing ENTER). Done correctly Excel will
wrap curly braces { } around the formula. Look for these braces in the
formula bar as a visual check that the formula has been correctly
array-entered. If you don't see the braces, then it hasn't been correctly
array-entered.

Anyway, here's an illustrative sample for your reference:
http://www.savefile.com/files/555218
Matching multiple criteria.xls

The multiplication of the various identical size criteria arrays, ie:
($A$2:$A$100=G1)*($B$2: $B$100=H1)*($C$2:$C$100=I1)*($D$2:$D$100=J1)
will produce a resultant array of zeros "0"'s with a single "1", eg:
{0,0,1,0,0}
The single "1" marks the position which satisfies all of the criteria

MATCH(1,{0,0,1,0,0},0) then returns the exact position of the "1" within the
resultant array, ie 3.

INDEX($E$2:$E$100,MATCH(...)) resolves to
INDEX($E$2:$E$100,3)
which returns the corresponding element (ie the 3rd item) within E2:E100 as
the final result.

Thank you Max,
I don't really see how it works yet, but I'll get there. Now, how do I
still run the same formula but, instead of returning the adjacent cell,
it returns the lowest value that also matches the rest of the criteria?
Do I add a MIN point? How?

--
frankjh19701

will return the minimum value from col E for all rows satisfying the
joint criteria:
($A$2:$A$100=G1)
($B$2:$B$100=H1)
($C$2:$C$100=I1)
($D$2:$D$100=J1)

On Mar 16, 4:21 am, frankjh19701 <frankjh19701.3f9...@excelbanter.com>
wrote:

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)),ROW(),"")
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.