MrExcel Publishing
Your One Stop for Excel Tips & Solutions

IF-function Limit??


Posted by Anthony on July 20, 2001 12:24 PM

Hello again! I've just realized there is a limit to the amount of arguments an If function can have. It's a simple If-Then formula. Do I have any alternatives?

Thanks

Anthony


Posted by Mark W. on July 20, 2001 12:25 PM

VLOOKUP()

Posted by Anthony on July 20, 2001 12:54 PM

Thanks for the response Mark! Would you mind providing me with a small example of how I would change a simple If statement into a VLOOKUP?

Thanks Again

Anthony

Posted by Mark W. on July 20, 2001 12:57 PM

Better yet...

Can you provide a sample of your IF conditions and
the value returned if TRUE? for the response Mark! Would you mind providing me with a small example of how I would change a simple If statement into a VLOOKUP? Again

Posted by Eric on July 20, 2001 2:18 PM

Concatentated IF functions (the pro's will hate this :-)

Sometimes I can't get VLOOKUP to do the same thing as IF (probably a mental issue), so one way to get more than one IF result is the following:
=if(a1,">=5","regular","")&if(a1,">7","+bonus!","")&if(a1,"<5","weak","")
where column (a) contains numbers, this will return multiple IF results into whatever column it is pasted. For instance if a number is >7 it will report "regular+bonus!" which is two if results.
I wonder if that helps?

Posted by Aladin Akyurek on July 20, 2001 2:31 PM

Re: Concatentated IF functions (the pro's will hate this :-)

Eric,

There are some examples of re-expressing NESTED IFs in terms of VLOOKUP in Archive2.

Aladin

========

Posted by Anthony on July 20, 2001 8:31 PM

Re: Better yet...

I have a spreadsheet that has code numbers which represent each month of the year. The file displays the months as numbers instead of text. I would prefer the latter. Therefore, I would like to state the following:

=IF(A2=1,"January",IF(A2=2,"February",IF(A2=3,"March")))........and so on. So being that there are limits to this function, I cannot retrieve all of the twelve months.

Any other suggestions?

Thanks

Anthony

Posted by Aladin Akyurek on July 21, 2001 12:28 AM

IF => VLOOKUP

Anthony,

This is one case where you can use VLOOKUP instead of many nested IFs.


=VLOOKUP(A2,{1,"January";2,"February";3,"March";4,"April";5,"May";6,"June";7,"July";8,"August";9,"September";10,"October";11,"November";12,"December"},2,0)

Aladin

Posted by Eric on July 21, 2001 11:29 AM

Thx Aladin (n-m)

Posted by Mark W. on July 23, 2001 6:10 AM

In addition to VLOOKUP...

...you can also use...

=CHOOSE(A2,"January","February","March",...)

or even simpier...

=TEXT(A2&"/1/01","mmmm")

Posted by Aladin Akyurek on July 23, 2001 7:45 AM

I would take the one that is simpler... (NT)

Posted by Anthony on July 23, 2001 1:28 PM

Worked great.......thanks guys!

Posted by Anthony on July 23, 2001 1:33 PM

Worked Perfect! Thank You!