if specific value from list A equals one of the values from list b...

B

broer konijn

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

Toppers

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
 
B

broer konijn

Let me clearify, since I cannot figure it out with this hint.

Suppose in worksheet 1 I have a list of 10 differennt data (specifi
days). Suppose in worksheet 2 I have a list of all daily data fo
several years. What I want is to mark/label the data in worksheet
that are equal to the list in worksheet 1. Thus, worksheet 2 has dail
chronological data, worksheet 1 has gaps between the data. My idea wa
to insert a colum and let the value in this colum on the line for equa
data become 1 and the others become 0. In that case worksheet 2 wil
contain a colum with a time serie of daily data and the first colu
indicates a 0 or 1, depending on whether the data is equal to one o
the list in worksheet 1.

Given this explanation, do you advise the same sollution.
Thanx again!


A1 contains value to be matched and curly brackets{} contains list o
values

=IF(ISERROR(MATCH(A1,{1,2,3,4,5,6,7,8,9,0},0)),"No match","Matched")


HTH
Click to expand...
 
T

Toppers

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
 
B

broer konijn

The logics are clear. Somehow it does not work (I see that U use
instead of ; b2w).

I added an example in excel.




If I understand your requirement correctly, you could use VLOOKUP (o
MATCH)
in Sheet2 to see if the Sheet2 data was present in Sheet1. If Yes, pu
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 said:
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 ha daily
chronological data, worksheet 1 has gaps between the data. My ide was
to insert a colum and let the value in this colum on the line fo 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!
Click to expand...

+-------------------------------------------------------------------
|Filename: Book2.zip
|Download: http://www.excelforum.com/attachment.php?postid=4876
+-------------------------------------------------------------------
Click to expand...
 
T

Toppers

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
 
B

broer konijn

Indeed, it works fabulously!:)
Thanx!

Ps: how do I offer you points for a good answer, if wanted?
 
T

Toppers

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