Many Conditions on an IF statement


Posted by Deirdre Marie on September 25, 2001 10:02 AM

I am confident this has been covered previously, but after a search I could not find the answer.

I have a spreadsheet which will contain approximately 500 rows of data.

I have column A reserved for CITY and column B reserved for Representative.

What I require is an IF statement that will say IF A="*city" then B="*representative"

Keep in mind there will be 500 rows of data with approximately 100 different cities.

I've tried this with the usual IF statements with no success - Per my understanding, you are limited on the conditions you can set.

Please advise.

Posted by Aladin Akyurek on September 25, 2001 10:07 AM

Care to elaborate this IF a bit or what you want that it does for you?

It looks like you want to know who is the representative given a city. That is, you want to retrieve the representative given a city.

Aladin

Posted by Deirdre Marie on September 25, 2001 10:13 AM

My apologies for not being more clear.

What I would like to happen is this:

When the user inputs the city information, the representative information is automatically inserted.

For instance, if I type "Hartford" into the city column, Excel will automatically insert "John Brown" into the representative portion for me.


Posted by Aladin Akyurek on September 25, 2001 10:33 AM

Still a bit unclear to me I'm afraid.

You have already a list of cities in A and associated representatives in B in some sheet.

Where do you want to enter "Hartford" and automatically get its representative "John Brown"? Not at the end of the list above I suppose, right?

If it's somewhere else and the representative must be retrieved from the list above, then use


=VLOOKUP(City,List,2,0)

where City is a name between double quotes or the cell where you enter a city and List the range of the list that you have in columns A and B.

If a city is not on the list, you'll get #N/A as result, otherwise the associated representative.

If you want to avoid getting #N/A, use

=IF(COUNTIF(List,City),VLOOKUP(City,List,2,0),"Not Found")


Is this what you are looking for?

Aladin



Posted by Rudy on September 25, 2001 2:02 PM

I agree w Mr. Aladin. You don't want to do this with an IF statement (too complicated). Go to some kind of table.