If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Looking up name with 2 criteria
A B C D E
First x 5 second x 0 third x 2 fourth x 2 fifth x 1 I am trying to get the A name with criteria of D and the lowest number. This list is about 100 items. So in the above case is would look through all of D for x's and the lowest E value which is 1 above. I was trying this: =INDEX(A1:A100,MATCH(MIN(E1:E100),G2:G19,0)*(D11 00="x"),0) The problem is it looks at the zero and returns second |
#2
|
|||
|
|||
Looking up name with 2 criteria
I got it close but it always looks for the lowest number which I need it to
look for the lowest number that has an "x" in that row: =INDEX(A1:A100,MATCH(MIN(E1:E100)&"x",E1:E100&D1 100,0)) "jtfalk" wrote: A B C D E First x 5 second x 0 third x 2 fourth x 2 fifth x 1 I am trying to get the A name with criteria of D and the lowest number. This list is about 100 items. So in the above case is would look through all of D for x's and the lowest E value which is 1 above. I was trying this: =INDEX(A1:A100,MATCH(MIN(E1:E100),G2:G19,0)*(D11 00="x"),0) The problem is it looks at the zero and returns second |
#3
|
|||
|
|||
Looking up name with 2 criteria
Hi,
Try this ARRAY formula =INDEX(A1:A5,MATCH(1,("x"=D15)*(MIN(IF(E1:E50,E 1:E5))=E1:E5),0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jtfalk" wrote: A B C D E First x 5 second x 0 third x 2 fourth x 2 fifth x 1 I am trying to get the A name with criteria of D and the lowest number. This list is about 100 items. So in the above case is would look through all of D for x's and the lowest E value which is 1 above. I was trying this: =INDEX(A1:A100,MATCH(MIN(E1:E100),G2:G19,0)*(D11 00="x"),0) The problem is it looks at the zero and returns second |
#4
|
|||
|
|||
Looking up name with 2 criteria
Okay - it is now working. Is there a way to use this can have it rank them in order of the lowest to the highest? So the list would take the 2 D's and put them in order in different cells in the lowest to highest order? "Mike H" wrote: Hi, Try this ARRAY formula =INDEX(A1:A5,MATCH(1,("x"=D15)*(MIN(IF(E1:E50,E 1:E5))=E1:E5),0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jtfalk" wrote: A B C D E First x 5 second x 0 third x 2 fourth x 2 fifth x 1 I am trying to get the A name with criteria of D and the lowest number. This list is about 100 items. So in the above case is would look through all of D for x's and the lowest E value which is 1 above. I was trying this: =INDEX(A1:A100,MATCH(MIN(E1:E100),G2:G19,0)*(D11 00="x"),0) The problem is it looks at the zero and returns second |
#5
|
|||
|
|||
Looking up name with 2 criteria
=INDEX(A1:A5,MATCH(1,("x"=D15)*(MIN(IF(E1:E50, E1:E5))=E1:E5),0))
That will return an error when the min number in the range is not associated with "x" and is less than the min number that is associated with "x". A......64 B......16 C..x..89 D......5 E..x...16 Based on that sample data the above formula returns #N/A. The correct result should be E. Try this (array entered): =INDEX(A1:A5,MATCH(1,(D15="x")*(E1:E5=MIN(IF(D1: D5="x",E1:E5))),0)) -- Biff Microsoft Excel MVP "Mike H" wrote in message ... Hi, Try this ARRAY formula =INDEX(A1:A5,MATCH(1,("x"=D15)*(MIN(IF(E1:E50,E 1:E5))=E1:E5),0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jtfalk" wrote: A B C D E First x 5 second x 0 third x 2 fourth x 2 fifth x 1 I am trying to get the A name with criteria of D and the lowest number. This list is about 100 items. So in the above case is would look through all of D for x's and the lowest E value which is 1 above. I was trying this: =INDEX(A1:A100,MATCH(MIN(E1:E100),G2:G19,0)*(D11 00="x"),0) The problem is it looks at the zero and returns second |
#6
|
|||
|
|||
Looking up name with 2 criteria
Thanks for that Biff, I never tested it properly.
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "T. Valko" wrote: =INDEX(A1:A5,MATCH(1,("x"=D15)*(MIN(IF(E1:E50, E1:E5))=E1:E5),0)) That will return an error when the min number in the range is not associated with "x" and is less than the min number that is associated with "x". A......64 B......16 C..x..89 D......5 E..x...16 Based on that sample data the above formula returns #N/A. The correct result should be E. Try this (array entered): =INDEX(A1:A5,MATCH(1,(D15="x")*(E1:E5=MIN(IF(D1: D5="x",E1:E5))),0)) -- Biff Microsoft Excel MVP "Mike H" wrote in message ... Hi, Try this ARRAY formula =INDEX(A1:A5,MATCH(1,("x"=D15)*(MIN(IF(E1:E50,E 1:E5))=E1:E5),0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jtfalk" wrote: A B C D E First x 5 second x 0 third x 2 fourth x 2 fifth x 1 I am trying to get the A name with criteria of D and the lowest number. This list is about 100 items. So in the above case is would look through all of D for x's and the lowest E value which is 1 above. I was trying this: =INDEX(A1:A100,MATCH(MIN(E1:E100),G2:G19,0)*(D11 00="x"),0) The problem is it looks at the zero and returns second . |
Thread Tools | |
Display Modes | |
|
|