If (city1 or city2 or city 3.....) result = state1

vinayak patil

New member
Joined
Apr 23, 2014
Messages
1
Reaction score
0
Points
0
Hi; I m working on a large sheet which has multiple employee records and their respective cities. i now want to add a new column which specifies the state they belong to. i was looking for any formula which can help me out in comparing the cities of employees and marking a state for them for ex

If (city1 or city2 or city3....) cell (E12) in the new column = StateA
if (city11 or city22 or city33...) the same cell as above(E12)=StateB.


so overall we have about 20 states and each state may have about 10 cities...

Ihave attched the file. there are two fields on the first sheet " employee name and work location" i need to add a 3rd field " circle" for some of the enteries i have manually enterd the cirlce value. but finding ithard to do manually for an entire sheet. also sheet2 has an example of circle and its associated locations. we make us of this information to identify which location belongs to which state and then manually enter them till now. any formulas that can be used like for ex

if (pune or kolhapur or satara... then Maharashtra)elseif (Belgaum or bangalore or hubli...then karnataka)else if... for 20 telecom cirlces..



Really appreciate ur time and suggestions...

thanx n regards....
 

Attachments

  • Demo.xlsx
    157.1 KB · Views: 13
You need to tidy -up the data. The State/City table should have the state on every row of the city, and you need to get rid of the Bangalore 1, Bangalore 2, etc., then it is a simple INDEX/MATCH.
 
Back
Top