Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#4
|
|||
|
|||
Quote:
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
|
|||
|
|||
Quote:
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
|
|||
|
|||
Quote:
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
Return Numeric Values Matching EXACT Date for Criteria | Excel Worksheet Functions | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
Excel - return a picture or range rows as the result of a formula | Excel Worksheet Functions | |||
Return result from multiple criteria | Excel Worksheet Functions |