IF function with 3 options

I am trying to write a formula with the following:
-if I9=0, then write 0
-if I9=501, then write 1
-if I9=502, then write 2

What would be the best way to write this formula? I am sure this should be pretty basic, but everything I try comes up with an error.

Thanks!

You could do

=IFERROR(MATCH(I9,{0,501,502},0)-1,"")

I tried both of those formulas and it comes up #N/A. I know it shouldn't be this hard and I am starting to feel like an idiot!

Originally Posted by Airka1129

I tried both of those formulas and it comes up #N/A.

It's not possible for the formula I posted to return #N/A, that's the purpose of the IFERROR funciton.

What version of XL are you using?
Is it possible for the cell to contain values other than 0 501 or 502?
Perhaps I9 isn't really a number, but a "Number stored as Text"
What does this return?
=ISNUMBER(I9)


Can you post a sample book?


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Originally Posted by martindwilson

=MOD(I9,500)

That's Clever

I am running Excel 2007. I have attached a sample book. The cell (now E2) is the results of another formula [=LEFT(D2,3)], so it may be considered text.

I have a list of about 600 different policy numbers, and I am just looking at the first 3 digits of them.

That worked! Thanks so much for your help! It is greatly appreciated!