If or not formula help

[notarobot]

I'm trying to create a formula that produces a certain value based on a list of options.

Essentially, I want to know whether a someone associated with a city should be placed in "Zone 1", "Zone 2", or "Zone 3". "Zone 1" and "Zone 2" have a set of values associated with them, and then "Zone 3" is everything else.

As an example, let's say Zone 1 represents a certain list of cities--Austin, New York, Philly, Zone 2 represents a certain list of cities--Seattle, Atlanta, Chicago, and Zone 3 is all other cities not listed in Zones 1 or 2.

Any help would be great!

 

[CyrusTheVirus]

welcome to the forums. try something like the below (copy/paste)...

E2: =IF(ISNUMBER(MATCH($D2,$A$2:$A$7,0)),VLOOKUP($D2,$A$2:$B$7,2,0),"Zone 3")

[TABLE="width: 326"]
<tbody>[TR]
[TD]Zones[/TD]
[TD]Cities[/TD]
[TD][/TD]
[TD]Cities[/TD]
[TD]Zone?[/TD]
[/TR]
[TR]
[TD]Austin[/TD]
[TD]Zone 1[/TD]
[TD][/TD]
[TD]New York[/TD]
[TD]Zone 1[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Zone 1[/TD]
[TD][/TD]
[TD]Philly[/TD]
[TD]Zone 1[/TD]
[/TR]
[TR]
[TD]Philly[/TD]
[TD]Zone 1[/TD]
[TD][/TD]
[TD]Chicago[/TD]
[TD]Zone 2[/TD]
[/TR]
[TR]
[TD]Seattle[/TD]
[TD]Zone 2[/TD]
[TD][/TD]
[TD]Los Angeles[/TD]
[TD]Zone 3[/TD]
[/TR]
[TR]
[TD]Atlanta[/TD]
[TD]Zone 2[/TD]
[TD][/TD]
[TD]Austin[/TD]
[TD]Zone 1[/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]Zone 2[/TD]
[TD][/TD]
[TD]Boston[/TD]
[TD]Zone 3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Here's another option, if the cell with your city might have more of an address:

Excel 2012

	A	B	C	D	E	F
Name	Addr	Zone	City	Zone
Al	100 Main New York	Zone 1	Austin	Zone 1
Bob	200 State Atlanta	Zone 2	New York	Zone 1
Carol	300 East Charleston	Zone 3	Philly	Zone 1
Diane	400 South Seattle	Zone 2	Seattle	Zone 2
Ed	Zone 3	Atlanta	Zone 2
Chicago	Zone 2
Portland	Zone 2

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IFERROR(LOOKUP(2,1/(FIND($E$2:$E$10,B2)*($E$2:$E$10<>"")),$F$2:$F$10),"Zone 3")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dfsobral]

This will work

create this table in A1:B6 for example

Austin Zone 1
New York Zone 1
Philly Zone 1
Seattle Zone 2
Atlanta Zone 2
Chicago Zone 2


=+IFERROR(VLOOKUP(<city to search>,$A$1:$B$6,2,FALSE),"Zone 3")

 

[CyrusTheVirus]

Inspired by Eric's formula... you could condense mine down to just the below:

=IFERROR(VLOOKUP($D2,$A$2:$B$7,2,0),"Zone 3")

 

[T. Valko]

One way...

Data Range
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]

A​

[/td][td="bgcolor: #c0c0c0"]

B​

[/td][td="bgcolor: #c0c0c0"]

C​

[/td][td="bgcolor: #c0c0c0"]

D​

[/td][td="bgcolor: #c0c0c0"]

E​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

1​

[/td][td]

Pittsburgh​

[/td][td]

Zone 3​

[/td][td]

------​

[/td][td]

Austin​

[/td][td]

Zone 1​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

2​

[/td][td][/td][td][/td][td][/td][td]

New York​

[/td][td]

Zone 1​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

3​

[/td][td][/td][td][/td][td][/td][td]

Philly​

[/td][td]

Zone 1​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

4​

[/td][td][/td][td][/td][td][/td][td]

Seattle​

[/td][td]

Zone 2​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

5​

[/td][td][/td][td][/td][td][/td][td]

Atlanta​

[/td][td]

Zone 2​

[/td][/tr]

[tr][td="bgcolor: #c0c0c0"]

6​

[/td][td][/td][td][/td][td][/td][td]

Chicago​

[/td][td]

Zone 2​

[/td][/tr]
[/table]

Create the table in D1:E6.

Then, this formula entered in B1:

=IFERROR(VLOOKUP(A1,D1:E6,2,0),"Zone 3")

 

[notarobot]

Awesome--thanks so much! These will definitely work well.

not to be picky, but rather curious, is there a way to write a formula without some lookup from an additional table?

 

[T. Valko]

not to be picky, but rather curious, is there a way to write a formula without some lookup from an additional table?

You could "build" the table directly into the formula or you could use nested IF functions.

If you have "many" cities to evaluate this could lead to a very long formula.

 

[notarobot]

Yeah... I couldn't quite get the nested if aspect down, especially to add a not zone 1 and 2 clause to label zone 3 cities. I just want to see what it would look like for future reference.

You could "build" the table directly into the formula or you could use nested IF functions.

If you have "many" cities to evaluate this could lead to a very long formula.

 

[63falcondude]

Yeah... I couldn't quite get the nested if aspect down, especially to add a not zone 1 and 2 clause to label zone 3 cities. I just want to see what it would look like for future reference.

Something along the lines of

=IF(OR(A1={"Austin","New York","Philly"}),"Zone 1",IF(OR(A1={"Seattle","Atlanta","Georgia"}),"Zone 2","Zone 3"))