Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Matches
How do I look up or match from the same table in the same cell. Not sure how
to explain so here is the formula I am trying to use but it gives me an error. =MATCH($D2,CA2:CA38,0),MATCH($E2,CA2:CA38,0) $D2 and $E2 are dates and I am trying to match them to a calendar I built. I would like the cell to display a 1 if one of the arguements are true and a 0 if both of them are false. Any suggestions? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Matches
This should work:
=IF(OR(ISERROR(MATCH($D2,CA2:CA38,0)),ISERROR(MATC H($E2,CA2:CA38,0))),0,1) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Ron St Jean" wrote: How do I look up or match from the same table in the same cell. Not sure how to explain so here is the formula I am trying to use but it gives me an error. =MATCH($D2,CA2:CA38,0),MATCH($E2,CA2:CA38,0) $D2 and $E2 are dates and I am trying to match them to a calendar I built. I would like the cell to display a 1 if one of the arguements are true and a 0 if both of them are false. Any suggestions? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Matches
=OR((COUNTIF(CA2:CA38,$D2))+(COUNTIF(CA2:CA38,$E2) ))*1
-- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Ron St Jean" wrote: How do I look up or match from the same table in the same cell. Not sure how to explain so here is the formula I am trying to use but it gives me an error. =MATCH($D2,CA2:CA38,0),MATCH($E2,CA2:CA38,0) $D2 and $E2 are dates and I am trying to match them to a calendar I built. I would like the cell to display a 1 if one of the arguements are true and a 0 if both of them are false. Any suggestions? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Matches
You could even do shorten my formula from
=OR((COUNTIF(CA2:CA38,$D2))+(COUNTIF(CA2:CA38,$E2) ))*1 to =MAX(1,COUNTIF(CA2:CA38,$D2),COUNTIF(CA2:CA38,$E2) ) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "John C" wrote: =OR((COUNTIF(CA2:CA38,$D2))+(COUNTIF(CA2:CA38,$E2) ))*1 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Ron St Jean" wrote: How do I look up or match from the same table in the same cell. Not sure how to explain so here is the formula I am trying to use but it gives me an error. =MATCH($D2,CA2:CA38,0),MATCH($E2,CA2:CA38,0) $D2 and $E2 are dates and I am trying to match them to a calendar I built. I would like the cell to display a 1 if one of the arguements are true and a 0 if both of them are false. Any suggestions? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Matches
Thanks for the help, but it returns a 1 in every cell. I am going to play
with it some more. "John C" wrote: You could even do shorten my formula from =OR((COUNTIF(CA2:CA38,$D2))+(COUNTIF(CA2:CA38,$E2) ))*1 to =MAX(1,COUNTIF(CA2:CA38,$D2),COUNTIF(CA2:CA38,$E2) ) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "John C" wrote: =OR((COUNTIF(CA2:CA38,$D2))+(COUNTIF(CA2:CA38,$E2) ))*1 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Ron St Jean" wrote: How do I look up or match from the same table in the same cell. Not sure how to explain so here is the formula I am trying to use but it gives me an error. =MATCH($D2,CA2:CA38,0),MATCH($E2,CA2:CA38,0) $D2 and $E2 are dates and I am trying to match them to a calendar I built. I would like the cell to display a 1 if one of the arguements are true and a 0 if both of them are false. Any suggestions? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Matches
My sample data, I put 2 dates in cells D2 and E2, and if either of those
dates occurred anywhere in the range of CA2:CA38 then it would return a 1, otherwise it would return a 0. Is this what you wanted? When neither the date in E2 or D2 were anywhere in the range, it returned a 0. Perhaps you need to clarify more, what data you have, what is in D2 and E2, what results you are getting, and what you expect to get. -- ** John C ** "Ron St Jean" wrote: Thanks for the help, but it returns a 1 in every cell. I am going to play with it some more. "John C" wrote: You could even do shorten my formula from =OR((COUNTIF(CA2:CA38,$D2))+(COUNTIF(CA2:CA38,$E2) ))*1 to =MAX(1,COUNTIF(CA2:CA38,$D2),COUNTIF(CA2:CA38,$E2) ) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "John C" wrote: =OR((COUNTIF(CA2:CA38,$D2))+(COUNTIF(CA2:CA38,$E2) ))*1 -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Ron St Jean" wrote: How do I look up or match from the same table in the same cell. Not sure how to explain so here is the formula I am trying to use but it gives me an error. =MATCH($D2,CA2:CA38,0),MATCH($E2,CA2:CA38,0) $D2 and $E2 are dates and I am trying to match them to a calendar I built. I would like the cell to display a 1 if one of the arguements are true and a 0 if both of them are false. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Multiple Matches | Excel Worksheet Functions | |||
Returning Multiple Matches | Excel Worksheet Functions | |||
Displaying multiple matches | Excel Worksheet Functions | |||
Multiple Matches | Excel Worksheet Functions | |||
Multiple matches using LOOKUP | Excel Discussion (Misc queries) |