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

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

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 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

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

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

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

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