# of nested "IF's" in Excel 97??? 2

[carrr]

I've hit a snag on an Excel project. I want to build a nested IF statement that will accommodate 17 conditions. (What I'm actually doing is telling a column to convert numeric county codes into text when coming into my spreadwheet). Of course, Excel 97 appears to only allow the user 7 IF statements. Any suggestions of how to get around this? Or am I taking the wrong approach entirely?
Thanks in advance...

 

[xlhelp]

It can be done. Need more info as to what you are trying to do.

Indu

 

[DaleWatson123321]

One solution would be to set up a LOOKUP table that would include your entire list of TEXT items.

Then the LOOKUP formula would reference each of the codes in your list.

If your codes are in column A, for example, place the LOOKUP formula in column B, and simple copy it down for the number of records.

Here's an example of a formula that will work. It would be entered into cell B3.

=VLOOKUP(A3,names,2,FALSE)

where "names" is a range name that refers to your list of names. Your list of names would have two columns. the first would have your code, and the second would have the TEXT.

Hope this helps. Please advise as to how you make out.

If it would help, I could email you an example file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[mcm103]

Try using a Select Case.

Select Case yourVariable
Case Value(What you are looking for)
code you want to execute
Case Value2
Case Value3
.
.
.
Case Value17
End Select

 

[carrr]

Thanks. Here goes...
I've got a simple spreadsheet with columns: CountyID, LastName, FirstName, ProgramID. I've added columns: County and ProgramOffice. CountyID and ProgramID both contain numeric codes for specific counties and program offices. I'd like to tell columns County and ProgramOffice to populate their cells with the text version of CountyID and ProgramID's numeric contents...like as follows:

CountyID County
0 Unknown
3 Bay
3 Bay
37 Leon etc, etc.

I've tried: =IF(CountyID=0,"Unknown",IF(CountyID=3,"Bay",IF(CountyID=7,"Calhoun",IF(CountyID=19,"Franklin",....
...but this blows up when the seventh IF is passed.

Let me know if this is enough info,
Thanks In Advance

 

[carrr]

Dale,
I think I follow your lead, and I'll be experimenting with your suggestions. I wouldn't mind, though, a sample file if you have the time to send it.
Thanks,

Roy Carr

roy_carr@dcf.state.fl.us

 

[xlhelp]

This should be

=IF(CountyID=0,"Unknown","&quot&IF(CountyID=3,"Bay","&quot&IF(CountyID=7,"Calhoun","&quot&IF(CountyID=19,"Franklin","&quot&....

You can go on with this until you reach the cell size limit.

HTH

Indu

 

[carrr]

Two great solutions, equally effective results.

Since I am, however, a creature quite committed to minimizing my keystrokes, I think I have to lean towards Dale's solution as most efficient, especially when my lists run longer (my fingers are still numb from the workout I gave them entering my list when testing xlhelp's solution.

Thanks again!