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

[frankjh19701]

I have both text in some cells and numbers in others across a row and
want to find a formula that will allow me to determine I
pre-determined criteria matches each other across the row, then retur
the result of the next cell in the row. For Example:

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, an
"4496" in Column D give me the vaule of Column E.

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

Any and all help would be greatly appreciated

 

[Max]

One way ..

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]

Typos, lines ..

Inputs entered in say, G1:K1
... inputs entered in G2:K2, G3:K3, etc, ..

should read as:

Inputs entered in say, G1:J1 ...
... inputs entered in G2:J2, G3:KJ3, etc, ...

---

 

[frankjh19701]

Typos, lines ..-

should read as:-

---

Is there any way you can walk me through this formula? I've been tryin
it every which way I could and I'm going nowhere. I entered it exactl
like you said, and all I get is Value#. If I'm looking for on
particular value in column A, that is also adjacent to a particula
value in column B, and so on until column D, I want to return the valu
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

 

[Max]

.. I entered it exactly like you said, and all I get is Value#

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.

---

 

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

 

[Max]

This expression, array-entered as before in say: L1
=MIN(IF(($A$2:$A$100=G1)*($B$2:$B$100=H1)*($C$2:$C$100=I1)*($D$2:$D
$100=J1),$E$2:$E$100))

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)

 

[Max]

Here's a revised interp on your orig. post 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)),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.