|
if specific value from list A equals one of the values from list b...
How do I create an If-then-else function in which If(certain value) equals either one of a specified list of 10 values The issue is not how the if then else works, the issue is how to compare with a list of several options (say 20 values)? The nested option does not work, since one cannot nest 8 statements. Thanx for the help! -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 |
if specific value from list A equals one of the values from list b
A1 contains value to be matched and curly brackets{} contains list of values
=IF(ISERROR(MATCH(A1,{1,2,3,4,5,6,7,8,9,0},0)),"No match","Matched") HTH "broer konijn" wrote: How do I create an If-then-else function in which If(certain value) equals either one of a specified list of 10 values The issue is not how the if then else works, the issue is how to compare with a list of several options (say 20 values)? The nested option does not work, since one cannot nest 8 statements. Thanx for the help! -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 |
if specific value from list A equals one of the values from list b...
Let me clearify, since I cannot figure it out with this hint. Suppose in worksheet 1 I have a list of 10 differennt data (specific days). Suppose in worksheet 2 I have a list of all daily data for several years. What I want is to mark/label the data in worksheet 2 that are equal to the list in worksheet 1. Thus, worksheet 2 has daily chronological data, worksheet 1 has gaps between the data. My idea was to insert a colum and let the value in this colum on the line for equal data become 1 and the others become 0. In that case worksheet 2 will contain a colum with a time serie of daily data and the first colum indicates a 0 or 1, depending on whether the data is equal to one of the list in worksheet 1. Given this explanation, do you advise the same sollution. Thanx again! Toppers Wrote: A1 contains value to be matched and curly brackets{} contains list of values =IF(ISERROR(MATCH(A1,{1,2,3,4,5,6,7,8,9,0},0)),"No match","Matched") HTH "broer konijn" wrote: How do I create an If-then-else function in which If(certain value) equals either one of a specified list of 10 values The issue is not how the if then else works, the issue is how to compare with a list of several options (say 20 values)? The nested option does not work, since one cannot nest 8 statements. Thanx for the help! -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 |
if specific value from list A equals one of the values from li
If I understand your requirement correctly, you could use VLOOKUP (or MATCH)
in Sheet2 to see if the Sheet2 data was present in Sheet1. If Yes, put 1 else 0. In sheet2 put this in cell requiring 1,0 answer e.g B2 =If(iserror(A2,Sheet!$A$1:$A$20,0)),0,1) This will match A" value (in Sheet2) with list in A1:A20 in Sheet1; if matched, result will 1 otherwise 0. Copy down for all cells. HTH "broer konijn" wrote: Let me clearify, since I cannot figure it out with this hint. Suppose in worksheet 1 I have a list of 10 differennt data (specific days). Suppose in worksheet 2 I have a list of all daily data for several years. What I want is to mark/label the data in worksheet 2 that are equal to the list in worksheet 1. Thus, worksheet 2 has daily chronological data, worksheet 1 has gaps between the data. My idea was to insert a colum and let the value in this colum on the line for equal data become 1 and the others become 0. In that case worksheet 2 will contain a colum with a time serie of daily data and the first colum indicates a 0 or 1, depending on whether the data is equal to one of the list in worksheet 1. Given this explanation, do you advise the same sollution. Thanx again! Toppers Wrote: A1 contains value to be matched and curly brackets{} contains list of values =IF(ISERROR(MATCH(A1,{1,2,3,4,5,6,7,8,9,0},0)),"No match","Matched") HTH "broer konijn" wrote: How do I create an If-then-else function in which If(certain value) equals either one of a specified list of 10 values The issue is not how the if then else works, the issue is how to compare with a list of several options (say 20 values)? The nested option does not work, since one cannot nest 8 statements. Thanx for the help! -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 |
if specific value from list A equals one of the values from list b...
The logics are clear. Somehow it does not work (I see that U use , instead of ; b2w). I added an example in excel. Toppers Wrote: If I understand your requirement correctly, you could use VLOOKUP (or MATCH) in Sheet2 to see if the Sheet2 data was present in Sheet1. If Yes, put 1 else 0. In sheet2 put this in cell requiring 1,0 answer e.g B2 =If(iserror(A2,Sheet!$A$1:$A$20,0)),0,1) This will match A" value (in Sheet2) with list in A1:A20 in Sheet1; if matched, result will 1 otherwise 0. Copy down for all cells. HTH "broer konijn" wrote: Let me clearify, since I cannot figure it out with this hint. Suppose in worksheet 1 I have a list of 10 differennt data (specific days). Suppose in worksheet 2 I have a list of all daily data for several years. What I want is to mark/label the data in worksheet 2 that are equal to the list in worksheet 1. Thus, worksheet 2 has daily chronological data, worksheet 1 has gaps between the data. My idea was to insert a colum and let the value in this colum on the line for equal data become 1 and the others become 0. In that case worksheet 2 will contain a colum with a time serie of daily data and the first colum indicates a 0 or 1, depending on whether the data is equal to one of the list in worksheet 1. Given this explanation, do you advise the same sollution. Thanx again! Toppers Wrote: A1 contains value to be matched and curly brackets{} contains list of values =IF(ISERROR(MATCH(A1,{1,2,3,4,5,6,7,8,9,0},0)),"No match","Matched") HTH "broer konijn" wrote: How do I create an If-then-else function in which If(certain value) equals either one of a specified list of 10 values The issue is not how the if then else works, the issue is how to compare with a list of several options (say 20 values)? The nested option does not work, since one cannot nest 8 statements. Thanx for the help! -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 +-------------------------------------------------------------------+ |Filename: Book2.zip | |Download: http://www.excelforum.com/attachment.php?postid=4876 | +-------------------------------------------------------------------+ -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 |
if specific value from list A equals one of the values from li
In B4 put:
=IF(ISERROR(MATCH(C4,$E$4:$E$6,0)),0,1) and copy down to B13 This works OK in your sample sheet. HTH "broer konijn" wrote: The logics are clear. Somehow it does not work (I see that U use , instead of ; b2w). I added an example in excel. Toppers Wrote: If I understand your requirement correctly, you could use VLOOKUP (or MATCH) in Sheet2 to see if the Sheet2 data was present in Sheet1. If Yes, put 1 else 0. In sheet2 put this in cell requiring 1,0 answer e.g B2 =If(iserror(A2,Sheet!$A$1:$A$20,0)),0,1) This will match A" value (in Sheet2) with list in A1:A20 in Sheet1; if matched, result will 1 otherwise 0. Copy down for all cells. HTH "broer konijn" wrote: Let me clearify, since I cannot figure it out with this hint. Suppose in worksheet 1 I have a list of 10 differennt data (specific days). Suppose in worksheet 2 I have a list of all daily data for several years. What I want is to mark/label the data in worksheet 2 that are equal to the list in worksheet 1. Thus, worksheet 2 has daily chronological data, worksheet 1 has gaps between the data. My idea was to insert a colum and let the value in this colum on the line for equal data become 1 and the others become 0. In that case worksheet 2 will contain a colum with a time serie of daily data and the first colum indicates a 0 or 1, depending on whether the data is equal to one of the list in worksheet 1. Given this explanation, do you advise the same sollution. Thanx again! Toppers Wrote: A1 contains value to be matched and curly brackets{} contains list of values =IF(ISERROR(MATCH(A1,{1,2,3,4,5,6,7,8,9,0},0)),"No match","Matched") HTH "broer konijn" wrote: How do I create an If-then-else function in which If(certain value) equals either one of a specified list of 10 values The issue is not how the if then else works, the issue is how to compare with a list of several options (say 20 values)? The nested option does not work, since one cannot nest 8 statements. Thanx for the help! -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 +-------------------------------------------------------------------+ |Filename: Book2.zip | |Download: http://www.excelforum.com/attachment.php?postid=4876 | +-------------------------------------------------------------------+ -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 |
if specific value from list A equals one of the values from list b...
Indeed, it works fabulously!:) Thanx! Ps: how do I offer you points for a good answer, if wanted? Toppers Wrote: In B4 put: =IF(ISERROR(MATCH(C4,$E$4:$E$6,0)),0,1) and copy down to B13 This works OK in your sample sheet. HTH "broer konijn" wrote: The logics are clear. Somehow it does not work (I see that U use , instead of ; b2w). I added an example in excel. Toppers Wrote: If I understand your requirement correctly, you could use VLOOKUP (or MATCH) in Sheet2 to see if the Sheet2 data was present in Sheet1. If Yes, put 1 else 0. In sheet2 put this in cell requiring 1,0 answer e.g B2 =If(iserror(A2,Sheet!$A$1:$A$20,0)),0,1) This will match A" value (in Sheet2) with list in A1:A20 in Sheet1; if matched, result will 1 otherwise 0. Copy down for all cells. HTH "broer konijn" wrote: Let me clearify, since I cannot figure it out with this hint. Suppose in worksheet 1 I have a list of 10 differennt data (specific days). Suppose in worksheet 2 I have a list of all daily data for several years. What I want is to mark/label the data in worksheet 2 that are equal to the list in worksheet 1. Thus, worksheet 2 has daily chronological data, worksheet 1 has gaps between the data. My idea was to insert a colum and let the value in this colum on the line for equal data become 1 and the others become 0. In that case worksheet 2 will contain a colum with a time serie of daily data and the first colum indicates a 0 or 1, depending on whether the data is equal to one of the list in worksheet 1. Given this explanation, do you advise the same sollution. Thanx again! Toppers Wrote: A1 contains value to be matched and curly brackets{} contains list of values =IF(ISERROR(MATCH(A1,{1,2,3,4,5,6,7,8,9,0},0)),"No match","Matched") HTH "broer konijn" wrote: How do I create an If-then-else function in which If(certain value) equals either one of a specified list of 10 values The issue is not how the if then else works, the issue is how to compare with a list of several options (say 20 values)? The nested option does not work, since one cannot nest 8 statements. Thanx for the help! -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 +-------------------------------------------------------------------+ |Filename: Book2.zip | |Download: http://www.excelforum.com/attachment.php?postid=4876 | +-------------------------------------------------------------------+ -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 |
if specific value from list A equals one of the values from li
Broer,
You can rate the thread on Excelforum or on the Microsoft Office Excel forum (Worksheet Functions). Whether rated or not, thanks for the feedback and I am pleased it's working for you. "broer konijn" wrote: Indeed, it works fabulously!:) Thanx! Ps: how do I offer you points for a good answer, if wanted? Toppers Wrote: In B4 put: =IF(ISERROR(MATCH(C4,$E$4:$E$6,0)),0,1) and copy down to B13 This works OK in your sample sheet. HTH "broer konijn" wrote: The logics are clear. Somehow it does not work (I see that U use , instead of ; b2w). I added an example in excel. Toppers Wrote: If I understand your requirement correctly, you could use VLOOKUP (or MATCH) in Sheet2 to see if the Sheet2 data was present in Sheet1. If Yes, put 1 else 0. In sheet2 put this in cell requiring 1,0 answer e.g B2 =If(iserror(A2,Sheet!$A$1:$A$20,0)),0,1) This will match A" value (in Sheet2) with list in A1:A20 in Sheet1; if matched, result will 1 otherwise 0. Copy down for all cells. HTH "broer konijn" wrote: Let me clearify, since I cannot figure it out with this hint. Suppose in worksheet 1 I have a list of 10 differennt data (specific days). Suppose in worksheet 2 I have a list of all daily data for several years. What I want is to mark/label the data in worksheet 2 that are equal to the list in worksheet 1. Thus, worksheet 2 has daily chronological data, worksheet 1 has gaps between the data. My idea was to insert a colum and let the value in this colum on the line for equal data become 1 and the others become 0. In that case worksheet 2 will contain a colum with a time serie of daily data and the first colum indicates a 0 or 1, depending on whether the data is equal to one of the list in worksheet 1. Given this explanation, do you advise the same sollution. Thanx again! Toppers Wrote: A1 contains value to be matched and curly brackets{} contains list of values =IF(ISERROR(MATCH(A1,{1,2,3,4,5,6,7,8,9,0},0)),"No match","Matched") HTH "broer konijn" wrote: How do I create an If-then-else function in which If(certain value) equals either one of a specified list of 10 values The issue is not how the if then else works, the issue is how to compare with a list of several options (say 20 values)? The nested option does not work, since one cannot nest 8 statements. Thanx for the help! -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 +-------------------------------------------------------------------+ |Filename: Book2.zip | |Download: http://www.excelforum.com/attachment.php?postid=4876 | +-------------------------------------------------------------------+ -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 -- broer konijn ------------------------------------------------------------------------ broer konijn's Profile: http://www.excelforum.com/member.php...o&userid=34402 View this thread: http://www.excelforum.com/showthread...hreadid=551311 |
| All times are GMT +1. The time now is 09:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com