IF statement with multiple values

[srwalesh]

I am looking for help on an excel formula in which i would like to do two things. I have two columns, one with a five digit number (column A), then column B will have the formula. If the value in column A is either 20800, 24900, 59000 or 60000, i would like it to return a 'INVALID' value. Then, for all other values to reference another sheet (sheet1) to pull a state name. My guess is to use a vlookup for the second portion, but currently i have 5 nested 'IF' statements and I am hoping there is a more precise formula to use.

Can anyone help?

thanks!

 

[63falcondude]

This formula assumes that your data has headers.

=IF(OR(A2="20800",A2="24900",A2="59000",A2="60000"),"INVALID","VALID")

Now, the "VALID" part of the formula can be changed to an index match/vlookup.

 

[srwalesh]

is there not a way to do it with an array?

 

[Weazel]

maybe...

=IF(OR(A1={20800;24900;59000;60000}),"invalid", VLOOKUP(A1,data,column,0))

 

[63falcondude]

What is the advantage of using an array here? I don't know too much about them.

 

[srwalesh]

the only reason i thought of it was to clean up the clutter a bit...

 

[srwalesh]

the array you gave me worked, thanks!