# Formula for: IF B1 = a range1 then return X, if range2 then return Y...

1. ## Formula for: IF B1 = a range1 then return X, if range2 then return Y...

I'm trying to create a function/formula that will return certain Text depending on a range of values. In the attached file is my sample. When someone types a zip code into B1 I would like cell C1 to respond "20 Mile Radius" if it falls within range A2:A59 on the "Zips" sheet, "25 Mile Radius" for B2:B93, "30 Mile Radius" for C2:C132, "40 Mile Radius" for D2:D236, and "50 Mile Radius" for E2:E394. I don't know if the list of zips have to be in a list like that or if there's a way to add them directly into a formula with commas or such.

Thanks!

2. ## Re: Formula for: IF B1 = a range1 then return X, if range2 then return Y...

I assume you want to return the lowest radius, right?
If a zip exists in both the 20 and 25 radius, you want the 20.

Try this array formla entered with CTRL + SHIFT + ENTER

=INDEX(Zips!\$A\$1:\$E\$1,MIN(IF(Zips!\$A\$2:\$E\$394=B1,COLUMN(Zips!\$A:\$E))))

If you actually want the larger radius, change MIN to MAX

4. ## Re: Formula for: IF B1 = a range1 then return X, if range2 then return Y...

The formula must be entered as an array by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

5. ## Re: Formula for: IF B1 = a range1 then return X, if range2 then return Y...

That worked. I just didn't know when to press CTRL+SHIFT+ENTER. Thank you so much!

6. ## Re: Formula for: IF B1 = a range1 then return X, if range2 then return Y...

You're welcome.

7. ## Re: Formula for: IF B1 = a range1 then return X, if range2 then return Y...

One last question...is there a way to have the cell be blank if a zip code other than those is entered? Right now if it's blank or a different zipcode is entered then it defaults to "20 Mile Radius"

Thanks,

Taylor

8. ## Re: Formula for: IF B1 = a range1 then return X, if range2 then return Y...

I sort of answered my own question. If I just add another column in front then that will be the MIN. However, can I change what is displayed or is it always going to display "0"?

Thanks,

Taylor

9. ## Re: Formula for: IF B1 = a range1 then return X, if range2 then return Y...

Try
=IF(OR(B1="",COUNTIF(Zips!\$A\$2:\$E\$394,B1)=0),"",INDEX(Zips!\$A\$1:\$E\$1,MIN(IF(Zips!\$A\$2:\$E\$394=B1,COLUMN(Zips!\$A:\$E)))))

10. ## Re: Formula for: IF B1 = a range1 then return X, if range2 then return Y...

Nevermind, it was a setting.

Have a great weekend!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1