Easier version of "IF", if many variables?

[Solveig]

Hi,
I have an excelsheet where I in cell A2 want to put the number 1 if a series of criteria is met in cell A1.
The challenge is that in cell A1 you can have around 100 different criterias that has to been considered - which is a bit long to put in an IF-function.

E.g if A1="1" or A1="15" or A1="X6" etc etc, I want to return the number 1 in B1.

Is there an easier way to do this than an incredible long IF function?

Thanks!!

 

[squidgeny]

It depends on the exact nature of your spreadsheet, but it sounds to me like a job for a VBA macro because then you could have:

Select Case Range("A1").Value
Case 1, 15, X6, etc etc
Range("B1") = 1
End Select

You could put this in a worksheet change event and make it consider the target cell each time.

 

[Peter_SSs]

If all the tests are just checking if A1 is one of say 100 values then another way would be to list the values in a vacant column then use a formula like this:

Excel Workbook
	A	B	C	D	E
1	15				1
2	1				15
3					X6
Check Values

 

[Solveig]

Thank you for your answers.
I am not used to using macros, so not sure if I can do that...

Regarding the countif-formula: This seems to work, but I realised that I have one set of variables that, if listed in A1, will return the number 1 in B1, and another set of variables that, if listed in A1, will return "" (blank) in B1.
How do I tackle this challenge?

 

[Peter_SSs]

Regarding the countif-formula: This seems to work, but I realised that I have one set of variables that, if listed in A1, will return the number 1 in B1, and another set of variables that, if listed in A1, will return "" (blank) in B1.
How do I tackle this challenge?

Doesn't my formula already deal with that?

Or do some values appear in both lists? If so, what should happen if one of those appears in A1?

 

[Solveig]

I'm not sure if it does.
The problem is that if the value in A1 is e.g 1, 15, 135, X6, X57 etc - it should return the value 1.
But if the value in A1 is e.g 2, 24, 356, X1, x23 etc - it should return "" (blank).
So I guess I have to define two columns of variables where, if the value of A1 is found in the first it will return 1, and if it is found in the other column it will return blank.

The value in A1 should not appear in both lists.

But what will the formula for this be?

 

[Jonmo1]

You can use Vlookup...See below
It is IMPORTANT to notice that F6 through F10 actually contain formulas =""
You can't just leave them blank, otherwise vlookup will return 0 instead of ""

Excel Workbook
	A	B	C	D	E	F
1	2				1	1
2					15	1
3					135	1
4					x6	1
5					x57	1
6					2
7					24
8					356
9					x1
10					x23
Sheet1

 

[Solveig]

Thank you!
I finally made it work!!

Have a nive day

 

[Peter_SSs]

I'm not sure just what you ended up using, but this is what I meant. The '1' values are in column E and the "" values are in column F.

To demonstrate, I have put the formula in B1 and copied down.

Note, however, that making the list in column F is really not necessary since if the column A value is not in column E then a "" will be returned anyway, without needing to check if the value is in column F.

Excel Workbook
	A	B	C	D	E	F
1	15	1			1	2
2	X57	1			15	24
3	24				135	356
4	1	1			X6	X1
5	2				X57	X23
6	X23
7	15	1
Check Values