I am trying to sort info based on the most recent database it appeared in.
Column A has the Master List, and outlying columns have other info. Column B
has the most recent list (call it 2006), Column C is 2005, Column D 2004. In
Column E I want to put the the most recent list that the items in Column A
show up in. I am trying to use IF(OR(EXACT))) to compare items in Column A
successively with each row. Something like:
=IF(OR(EXACT(A1,B:B)),"2006",IF(OR(EXACT(A1,C:C)), "2005",IF(OR(EXACT(A1,D:D)),"2004",
"NEW"))). Getting #NUM error. Tried CONTROL-SHIFT-ENTER at end, but not sure
if problem is all are not arrayed.

=IF(ISNUMBER(MATCH(A1,$B$1:$B$6500,0)) is what you want.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"archsmooth" wrote:

I am trying to sort info based on the most recent database it appeared in.
Column A has the Master List, and outlying columns have other info. Column B
has the most recent list (call it 2006), Column C is 2005, Column D 2004. In
Column E I want to put the the most recent list that the items in Column A
show up in. I am trying to use IF(OR(EXACT))) to compare items in Column A
successively with each row. Something like:
=IF(OR(EXACT(A1,B:B)),"2006",IF(OR(EXACT(A1,C:C)), "2005",IF(OR(EXACT(A1,D:D)),"2004",
"NEW"))). Getting #NUM error. Tried CONTROL-SHIFT-ENTER at end, but not sure
if problem is all are not arrayed.

I actually got it to work using:
=IF(OR(EXACT(A1,$B$1:$B$6500)),"2007",IF(OR(EXACT( A1,$C$1:$C$6500)),"2006","NEW")) with CONT-SHIFT-ENTER, but will test yours for future use.

"Dave F" wrote:

=IF(ISNUMBER(MATCH(A1,$B$1:$B$6500,0)) is what you want.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"archsmooth" wrote:

I am trying to sort info based on the most recent database it appeared in.
Column A has the Master List, and outlying columns have other info. Column B
has the most recent list (call it 2006), Column C is 2005, Column D 2004. In
Column E I want to put the the most recent list that the items in Column A
show up in. I am trying to use IF(OR(EXACT))) to compare items in Column A
successively with each row. Something like:
=IF(OR(EXACT(A1,B:B)),"2006",IF(OR(EXACT(A1,C:C)), "2005",IF(OR(EXACT(A1,D:D)),"2004",
"NEW"))). Getting #NUM error. Tried CONTROL-SHIFT-ENTER at end, but not sure
if problem is all are not arrayed.

This also appeared to work, array entered.

=CHOOSE(MIN(IF(EXACT(A1,B2:C3),{1,2},3)),2007,2006 ,"NEW")


"archsmooth" wrote:

I actually got it to work using:
=IF(OR(EXACT(A1,$B$1:$B$6500)),"2007",IF(OR(EXACT( A1,$C$1:$C$6500)),"2006","NEW")) with CONT-SHIFT-ENTER, but will test yours for future use.

"Dave F" wrote:

=IF(ISNUMBER(MATCH(A1,$B$1:$B$6500,0)) is what you want.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"archsmooth" wrote:

I am trying to sort info based on the most recent database it appeared in.
Column A has the Master List, and outlying columns have other info. Column B
has the most recent list (call it 2006), Column C is 2005, Column D 2004. In
Column E I want to put the the most recent list that the items in Column A
show up in. I am trying to use IF(OR(EXACT))) to compare items in Column A
successively with each row. Something like:
=IF(OR(EXACT(A1,B:B)),"2006",IF(OR(EXACT(A1,C:C)), "2005",IF(OR(EXACT(A1,D:D)),"2004",
"NEW"))). Getting #NUM error. Tried CONTROL-SHIFT-ENTER at end, but not sure
if problem is all are not arrayed.