# Easier version of "IF", if many variables?

#### Solveig

##### New Member
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!!

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.

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
ABCDE
1151
2115
3X6
Check Values

Last edited:
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?

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?

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?

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
ABCDEF
12 11
2151
31351
4x61
5x571
62
724
8356
9x1
10x23
Sheet1

Thank you!

Have a nive day

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
ABCDEF
115112
2X5711524
324135356
411X6X1
52X57X23
6X23
7151
Check Values

Replies
0
Views
128
Replies
6
Views
250
Replies
6
Views
307
Replies
1
Views
203
Replies
1
Views
153

1,203,069
Messages
6,053,352
Members
444,655
Latest member
didr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back