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

I have both text in some cells and numbers in others across a row and I want to find a formula that will allow me to determine IF pre-determined criteria matches each other across the row, then return 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, 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.

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

One way ..

Assuming source data as posted, within rows 2 to 100

Inputs entered in say, G1:K1 a 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" wrote in message
...

I have both text in some cells and numbers in others across a row and I
want to find a formula that will allow me to determine IF
pre-determined criteria matches each other across the row, then return
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, 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

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

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, ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

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

.. 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:
Put in L1, array-enter (press CTRL+SHIFT+ENTER):

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"frankjh19701" wrote in message
...
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.

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?

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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Mar 16, 4:21 am, frankjh19701
wrote:
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

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

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)),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 in message
...

I have both text in some cells and numbers in others across a row and I
want to find a formula that will allow me to determine IF
pre-determined criteria matches each other across the row, then return
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, 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

O.K. I've got a new question on this old problem. What if I wanted to add up the values the formula found? For ex.
In Column A there was Petro, as well as other names
In Column B there was Penn, as well as other names
And I wanted to add the values in another Column, let's say Column T that correspond with the Petro from Column A and the Penn in Column B.
Basically, find that whenever there is a Petro in Column A and a Penn in Column be - Add together the values found in column T. And total them in some cell for reference later. Any ideas?