|
IF-OR Functions
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-OR Functions
=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. |
IF-OR Functions
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. |
IF-OR Functions
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. |
| All times are GMT +1. The time now is 05:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com