G
Guest
What type of formula would respond to the following conditions 12 (excel
seems to only permit up to 7...is it possible to get around that?):
seems to only permit up to 7...is it possible to get around that?):
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jerry W. Lewis
You would have to either write a function in VBA or use helper cells to
do it.
I am not aware of any Excel function or combination of functions that
can test for "Any cell in Column AA=A1" If you are willing to restrict
to a subset of a column, then you could use an array formula like
IF(COUNT(FIND(A1,AA1:AA100)),"found","not found")
but you would quickly run out of nesting levels. Array formulas must be
array entered (Ctrl-Shift-Enter).
Jerry
do it.
I am not aware of any Excel function or combination of functions that
can test for "Any cell in Column AA=A1" If you are willing to restrict
to a subset of a column, then you could use an array formula like
IF(COUNT(FIND(A1,AA1:AA100)),"found","not found")
but you would quickly run out of nesting levels. Array formulas must be
array entered (Ctrl-Shift-Enter).
Jerry
J
JulieD
haven't had time to think through a solution, but i just wanted to respond
to Jerry's post about
e.g.
=COUNTIF(AA:AA,A1)
will give you the number of times it is found and
=IF(COUNTIF(AA:AA,A1)>=1,"found',"not found")
will give you whether or not it is found
hopefully will have time for a more detailed answer later on ...
to Jerry's post about
the countif function can be used for thisI am not aware of any Excel function or combination of functions that can
test for "Any cell in Column AA=A1"
e.g.
=COUNTIF(AA:AA,A1)
will give you the number of times it is found and
=IF(COUNTIF(AA:AA,A1)>=1,"found',"not found")
will give you whether or not it is found
hopefully will have time for a more detailed answer later on ...
G
Guest
What's the order of priority here Alex? Or are your test results mutually
exclusive?
What if values in column AA match A1 and A2 and/or A3?
What if values in AB = B1 and/or B2 and/or B3?
If there is no chance of multiple tests being true, you can use something
like this
=(AND(COUNTIF(A:A,A1)>0,COUNTIF(AB:AB,B1)>0) *555) +
(AND(COUNTIF(A:A,A2)>0,COUNTIF(AB:AB,B1)>0) *666) +
---finish your other 10 conditions with the same order
exclusive?
What if values in column AA match A1 and A2 and/or A3?
What if values in AB = B1 and/or B2 and/or B3?
If there is no chance of multiple tests being true, you can use something
like this
=(AND(COUNTIF(A:A,A1)>0,COUNTIF(AB:AB,B1)>0) *555) +
(AND(COUNTIF(A:A,A2)>0,COUNTIF(AB:AB,B1)>0) *666) +
---finish your other 10 conditions with the same order
G
Guest
This will give an error message if there is not a match in AA or AB but try
putting this into C!
=CHOOSE(IF(COUNTIF(AB:AB,B1)>0,0,IF(COUNTIF(AB:AB,B2)>0,3,IF(COUNTIF(AB:AB,B3)>0,6,IF(COUNTIF(AB:AB,B4)>0,9,12))))+IF(COUNTIF(AA:AA,A1)>0,1,IF(COUNTIF(AA:AA,A2)>0,2,IF(COUNTIF(AA:AA,A3)>0,3,12))),555,666,777,888,999,0,111,222,333,444,1212,2323,4545)
If you need something in C1 when there is no match the equation could be
modified.
If there is always to be a match in one or both coulumns the equation can be
simplified.
putting this into C!
=CHOOSE(IF(COUNTIF(AB:AB,B1)>0,0,IF(COUNTIF(AB:AB,B2)>0,3,IF(COUNTIF(AB:AB,B3)>0,6,IF(COUNTIF(AB:AB,B4)>0,9,12))))+IF(COUNTIF(AA:AA,A1)>0,1,IF(COUNTIF(AA:AA,A2)>0,2,IF(COUNTIF(AA:AA,A3)>0,3,12))),555,666,777,888,999,0,111,222,333,444,1212,2323,4545)
If you need something in C1 when there is no match the equation could be
modified.
If there is always to be a match in one or both coulumns the equation can be
simplified.
D
Domenic
One way would be to use a helper column. Try the following (formulas
need to be confirmed with CONTROL+SHIFT+ENTER , not just ENTER)...
Helper column:
AD1:
=INDEX({555,666,777},MATCH(1,(COUNTIF(AA:AA,A1:A3)>0)*(COUNTIF(AB:AB,B1)>
0),0))
AD2:
=INDEX({888,999,0},MATCH(1,(COUNTIF(AA:AA,A1:A3)>0)*(COUNTIF(AB:AB,B2)>0)
,0))
AD3:
=INDEX({111,222,333},MATCH(1,(COUNTIF(AA:AA,A1:A3)>0)*(COUNTIF(AB:AB,B3)>
0),0))
AD4:
=INDEX({1212,2323,4545},MATCH(1,(COUNTIF(AA:AA,A1:A3)>0)*(COUNTIF(AB:AB,B
4)>0),0))
This column can be hidden, if so desired. Alternatively, you can choose
'White' as your font color to hide these cells.
Desired result:
AE1:
=INDEX(AD:AD,MATCH(TRUE,AD1:AD4>=0,0))
Hope this helps!
need to be confirmed with CONTROL+SHIFT+ENTER , not just ENTER)...
Helper column:
AD1:
=INDEX({555,666,777},MATCH(1,(COUNTIF(AA:AA,A1:A3)>0)*(COUNTIF(AB:AB,B1)>
0),0))
AD2:
=INDEX({888,999,0},MATCH(1,(COUNTIF(AA:AA,A1:A3)>0)*(COUNTIF(AB:AB,B2)>0)
,0))
AD3:
=INDEX({111,222,333},MATCH(1,(COUNTIF(AA:AA,A1:A3)>0)*(COUNTIF(AB:AB,B3)>
0),0))
AD4:
=INDEX({1212,2323,4545},MATCH(1,(COUNTIF(AA:AA,A1:A3)>0)*(COUNTIF(AB:AB,B
4)>0),0))
This column can be hidden, if so desired. Alternatively, you can choose
'White' as your font color to hide these cells.
Desired result:
AE1:
=INDEX(AD:AD,MATCH(TRUE,AD1:AD4>=0,0))
Hope this helps!
seems to only permit up to 7...is it possible to get around that?):What type of formula would respond to the following conditions 12 (excel
IF Any Cell in Column AA= A1 and Any Cell in Column AB =B1 then C1=555
IF Any Cell in Column AA= A2 and Any Cell Column AB =B1 then C1=666
IF Any Cell in Column AA= A3 and Any Cell Column AB =B1 then C1=777
IF Any Cell in Column AA= A1 and Any Cell in Column AB =B2 then C1=888
IF Any Cell in Column AA= A2 and Any Cell in Column AB =B2 then C1=999
IF Any Cell in Column AA= A3 and Any Cell Column AB =B2 then C1=000
IF Any Cell in Column AA= A1 and Any Cell in Column AB =B3 then C1=111
IF Any Cell in Column AA= A2 and Any Cell in Column AB =B3 then C1=222
IF Any Cell in Column AA= A3 and Any Cell Column AB =B3 then C1=333
IF Any Cell in Column AA= A1 and Any Cell in Column AB =B4 then C1=1212
IF Any Cell in Column AA= A2 and Any Cell in Column AB =B4 then C1=2323
IF Any Cell in Column AA= A3 and Any Cell Column AB =B4 then C1=4545
[/QUOTE]help me please?
Alex
G
Guest
after reading Duke's comments, I did assume (shudder) that the order you put
the matches was the priority. It would be easy to change the equation for a
different priority.
the matches was the priority. It would be easy to change the equation for a
different priority.
J
Jerry W. Lewis
Not sure what I was thinking when I used COUNT(FIND()) instead of
COUNTIF, but COUNTIF(), does not work if you pass an entire column as
the range.
Jerry
COUNTIF, but COUNTIF(), does not work if you pass an entire column as
the range.
Jerry
H
Harlan Grove
Jerry W. Lewis wrote...
Assuming the comma immediately after COUNTIF() was a typo, your
statement is incorrect if you're referring to the first argument.
COUNTIF's first argument must be a single area range, so the problem
with entire column ranges being converted to arrays is finessed. On the
other hand, you can't use an entire column range as COUNTIF's second
argument, so the standard idiom for counting distinct entries in a
range, e.g.,
=SUMPRODUCT(1/COUNTIF(A:A,A:A))
won't work.
For example, enter =ROW() in A1:A65536, and enter
=COUNTIF(A:A,">4")
and
=COUNTIF(A:A,"<100")
in other cells. I get 65532 and 99, respectively. Don't you?
....Not sure what I was thinking when I used COUNT(FIND()) instead of
COUNTIF, but COUNTIF(), does not work if you pass an entire column as
the range.
Assuming the comma immediately after COUNTIF() was a typo, your
statement is incorrect if you're referring to the first argument.
COUNTIF's first argument must be a single area range, so the problem
with entire column ranges being converted to arrays is finessed. On the
other hand, you can't use an entire column range as COUNTIF's second
argument, so the standard idiom for counting distinct entries in a
range, e.g.,
=SUMPRODUCT(1/COUNTIF(A:A,A:A))
won't work.
For example, enter =ROW() in A1:A65536, and enter
=COUNTIF(A:A,">4")
and
=COUNTIF(A:A,"<100")
in other cells. I get 65532 and 99, respectively. Don't you?
J
Jerry W. Lewis
The OP's application was an entire column as the second argument.
COUNTIF will not work for that.
Jerry
COUNTIF will not work for that.
Jerry
H
Harlan Grove
Jerry W. Lewis wrote..
Not in my reading. To repeat,
I'd rephrase this as if any cell in col AB = value of cell B1, then set
cell C1 to a value based on which of the values of A1:A3 appear in col
AA. If no col AB match for B1, then try B2, B3, B4, etc.
It'd be a stretch to interpret this as the OP wanting to check all
cells in col B. Possible, perhaps, but unlikely. Or perhaps the OP
multiposted in another thread or ng, and you're referring to that.
Anyway, the point here is that the col AA -> C1 value is a lookup, if a
complicated one, and col AB and cells in col B determine the lookup
results.
C1 [array formula]:
=INDEX({555,666,777;888,999,0;111,222,333;1212,2323,4545},
MATCH(TRUE,COUNTIF($AB:$AB,B1:B4)>0,0),
MATCH(TRUE,COUNTIF($AA:$AA,A1:A3)>0,0))
It'd be better to use a range to store the return values for cell C1,
but the main point is that this can be done in a *SINGLE* formula, and
the matrix of return values can grow way beyond just 12 choices.
Indeed, this begs the question whether the ideal data structure would
be having A1:A3 in A2:A4 instead, B1:B4 in B1:E1 instead, and the
return values as above in B2:E4, making this a more obvious 2D lookup.
....The OP's application was an entire column as the second argument.
COUNTIF will not work for that.
Not in my reading. To repeat,
What type of formula would respond to the following conditions 12 (excel
seems to only permit up to 7...is it possible to get around that?):
IF Any Cell in Column AA= A1 and Any Cell in Column AB =B1 then C1=555
IF Any Cell in Column AA= A2 and Any Cell Column AB =B1 then C1=666
IF Any Cell in Column AA= A3 and Any Cell Column AB =B1 then C1=777
IF Any Cell in Column AA= A1 and Any Cell in Column AB =B2 then C1=888
IF Any Cell in Column AA= A2 and Any Cell in Column AB =B2 then C1=999
IF Any Cell in Column AA= A3 and Any Cell Column AB =B2 then C1=000
IF Any Cell in Column AA= A1 and Any Cell in Column AB =B3 then C1=111
IF Any Cell in Column AA= A2 and Any Cell in Column AB =B3 then C1=222
IF Any Cell in Column AA= A3 and Any Cell Column AB =B3 then C1=333
IF Any Cell in Column AA= A1 and Any Cell in Column AB =B4 then C1=1212
IF Any Cell in Column AA= A2 and Any Cell in Column AB =B4 then C1=2323
IF Any Cell in Column AA= A3 and Any Cell Column AB =B4 then C1=4545
I'd rephrase this as if any cell in col AB = value of cell B1, then set
cell C1 to a value based on which of the values of A1:A3 appear in col
AA. If no col AB match for B1, then try B2, B3, B4, etc.
It'd be a stretch to interpret this as the OP wanting to check all
cells in col B. Possible, perhaps, but unlikely. Or perhaps the OP
multiposted in another thread or ng, and you're referring to that.
Anyway, the point here is that the col AA -> C1 value is a lookup, if a
complicated one, and col AB and cells in col B determine the lookup
results.
C1 [array formula]:
=INDEX({555,666,777;888,999,0;111,222,333;1212,2323,4545},
MATCH(TRUE,COUNTIF($AB:$AB,B1:B4)>0,0),
MATCH(TRUE,COUNTIF($AA:$AA,A1:A3)>0,0))
It'd be better to use a range to store the return values for cell C1,
but the main point is that this can be done in a *SINGLE* formula, and
the matrix of return values can grow way beyond just 12 choices.
Indeed, this begs the question whether the ideal data structure would
be having A1:A3 in A2:A4 instead, B1:B4 in B1:E1 instead, and the
return values as above in B2:E4, making this a more obvious 2D lookup.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.