+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    01-09-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    7

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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

  3. #3
    Registered User
    Join Date
    01-09-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    7

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

    I just receive #Value!
    Attached Files Attached Files
    Last edited by taylorjkeen; 01-09-2015 at 05:37 PM.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #5
    Registered User
    Join Date
    01-09-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    7

    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. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

    You're welcome.

  7. #7
    Registered User
    Join Date
    01-09-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    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. #8
    Registered User
    Join Date
    01-09-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    7

    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. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #10
    Registered User
    Join Date
    01-09-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    7

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

    Nevermind, it was a setting.

    Have a great weekend!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Range1 = Range2 and Range2 = Range1
    By rawcode in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-10-2012, 04:28 AM
  2. [SOLVED] if range1=range2 then range A=RangeB
    By denvernuggets15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2012, 12:55 AM
  3. If Range1 Not Hidden on Sheet1 Then Show Range2 on Sheet2
    By Johnathan in forum Excel General
    Replies: 1
    Last Post: 01-09-2012, 11:14 PM
  4. Transfer Item From Range1 to Range2 Using ListBox
    By hifliers in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-22-2009, 06:13 AM
  5. ???Sumif(Range1,Range1<Range2,Range1)???
    By Nick in forum Excel General
    Replies: 2
    Last Post: 04-14-2006, 02:30 PM

Bookmarks

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