software

I am using the following formula and if there is more than 8, it gives
me an error msg each time (and I am sure to add additional )'s at the
end. Any suggestions how I could get it to allow as many as I want.
Thanks in advance.

=IF(F5=quot;1Kquot;,quot;51126quot;,IF(F5=quot;2ACquot;,quot;36320quot;,IF(F5=quot;0AP quot;,quot;49003quot;,IF(F5=quot;3ACquot;,quot;36316quot;,IF(F5=quot;9CHquot;,quot;45284quot;, IF(F5=quot;1DBquot;,quot;318quot;,IF(F5=quot;6ACquot;,quot;53570quot;,IF(F5=quot;149quot;, quot;1045quot;,quot;quot;))))))))

I am wanting to be able to put as many as I want. Thanks in advance.
Mcr1--
mcr1
------------------------------------------------------------------------
mcr1's Profile: www.excelforum.com/member.php...oamp;userid=15496
View this thread: www.excelforum.com/showthread...hreadid=498860On Fri, 6 Jan 2006 15:14:48 -0600, mcr1
gt; wrote:

gt;
gt;I am using the following formula and if there is more than 8, it gives
gt;me an error msg each time (and I am sure to add additional )'s at the
gt;end. Any suggestions how I could get it to allow as many as I want.
gt;Thanks in advance.
gt;
gt;=IF(F5=quot;1Kquot;,quot;51126quot;,IF(F5=quot;2ACquot;,quot;36320quot;,IF(F5=quot;0A Pquot;,quot;49003quot;,IF(F5=quot;3ACquot;,quot;36316quot;,IF(F5=quot;9CHquot;,quot;45284quot; ,IF(F5=quot;1DBquot;,quot;318quot;,IF(F5=quot;6ACquot;,quot;53570quot;,IF(F5=quot;149quot; ,quot;1045quot;,quot;quot;))))))))
gt;
gt;I am wanting to be able to put as many as I want. Thanks in advance.

What you're doing is called quot;nestingquot;, and it has a limit.
Specifically:

quot;Nesting level limits: A formula can contain up to seven levels of
nested functions.quot;

I suggest that instead of doing it that way, you create a table
somewhere in your workbook and use VLookups to compare F5's value to
that table. It makes for much neater and less error-prone formulas.

[F1] help has a pretty good worked example explaining how to use the
function if you haven't done so before.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *


Can't be done with an IF as it only allows for 7 nested if in a formula.
What you can do is set up you data somewhere in an out of the way place
of your worksheet or workbook then use a VLOOKUP formula.

=VLOOKUP(A1,your_table,2,0)

HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=498860Excel only allows 7 nested IF statements with no exceptions. There are some
work arounds though with the VLOOKUP functions. The easiest way is to make a
table in another sheet and reference that table. It is possible to insert
the table in the formula itself like this...
=VLOOKUP(F5,{quot;1Kquot;,quot;51126quot;;quot;2ACquot;,quot;36320quot;;quot;0APquot;,quot;490 03quot;;quot;3ACquot;,quot;36316quot;;quot;9CHquot;,quot;45284quot;;quot;1DBquot;,quot;318quot;;quot;6ACquot;, quot;53570quot;;quot;149quot;,quot;1045quot;},2,0)

and if you want the quot;quot; part you need something like this...
=IF(ISERROR(VLOOKUP(F5,{quot;1Kquot;,quot;51126quot;;quot;2ACquot;,quot;36320quot; ;quot;0APquot;,quot;49003quot;;quot;3ACquot;,quot;36316quot;;quot;9CHquot;,quot;45284quot;;quot;1DBquot;,quot; 318quot;;quot;6ACquot;,quot;53570quot;;quot;149quot;,quot;1045quot;},2,0)),quot;quot;,VLOOKUP( F5,{quot;1Kquot;,quot;51126quot;;quot;2ACquot;,quot;36320quot;;quot;0APquot;,quot;49003quot;;quot;3ACquot; ,quot;36316quot;;quot;9CHquot;,quot;45284quot;;quot;1DBquot;,quot;318quot;;quot;6ACquot;,quot;53570quot;;quot; 149quot;,quot;1045quot;},2,0))

Of course if you use a table instead the formula will be a lot cleaner and
will look like this
=VLOOKUP(F5,A1:B8,2,FALSE)
or this to add the quot;quot; part
=IF(ISERROR(VLOOKUP(F5,A1:B8,2,FALSE)),quot;quot;,VLOOKUP( F5,A1:B8,2,FALSE))

quot;mcr1quot; wrote:

gt;
gt; I am using the following formula and if there is more than 8, it gives
gt; me an error msg each time (and I am sure to add additional )'s at the
gt; end. Any suggestions how I could get it to allow as many as I want.
gt; Thanks in advance.
gt;
gt; =IF(F5=quot;1Kquot;,quot;51126quot;,IF(F5=quot;2ACquot;,quot;36320quot;,IF(F5=quot;0AP quot;,quot;49003quot;,IF(F5=quot;3ACquot;,quot;36316quot;,IF(F5=quot;9CHquot;,quot;45284quot;, IF(F5=quot;1DBquot;,quot;318quot;,IF(F5=quot;6ACquot;,quot;53570quot;,IF(F5=quot;149quot;, quot;1045quot;,quot;quot;))))))))
gt;
gt; I am wanting to be able to put as many as I want. Thanks in advance.
gt; Mcr1
gt;
gt;
gt; --
gt; mcr1
gt; ------------------------------------------------------------------------
gt; mcr1's Profile: www.excelforum.com/member.php...oamp;userid=15496
gt; View this thread: www.excelforum.com/showthread...hreadid=498860
gt;
gt;


Never done that. I have no idea how to make a table. Any suggestions?
I cant even figure it out with the help function. Thanks again--
mcr1
------------------------------------------------------------------------
mcr1's Profile: www.excelforum.com/member.php...oamp;userid=15496
View this thread: www.excelforum.com/showthread...hreadid=498860
Find a blank spot somewhere in your worksheet, col (1) put your code,
col (2) put the corresponding number.

i.e
Col(1)-Col(2)
1k - 51126
2Ac - 36320
OAP - 49003
etc.....

then select your table, hit CTRL F3, in the quot;Names in workbookquot; text
box type a name for your table....say table1 and hit OK. Now for your
formula use:

=VLOOKUP(F5,table1,2,0)

Good Luck
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=498860On Fri, 6 Jan 2006 17:37:03 -0600, mcr1
gt; wrote:

gt;Never done that. I have no idea how to make a table. Any suggestions?
gt;I cant even figure it out with the help function. Thanks again

The Help makes it about as clear as it can be, but let's try an
example that's customised to your own needs and see whether that makes
it easier.

Start with a blank workbook. Now, on sheet 1, go to cell A1 and enter
the text quot;Cell Valuequot;. (Without the quotes)

In cell B1, enter quot;Lookup valuequot;. These will be your table headings.
(Which aren't really needed, but they make it easier to follow what's
going on.)

Now format the cells below those as Text. (Select the cells, then go
Format -gt; Cells and set the Number Format as Text.) You need to do
this because some of your lookup values have leading zeroes.

Now enter the following values into the cells specified below:
Cell Value
A2 1K
B2 51126
A3 2AC
B3 36320
A4 0AP
B4 49003
A5 3AC
B5 36316

So you can see that you have the IF condition in column A, and the
value that you want in column B right alongside it. This is your
lookup table.

You can run this lookup table down as far as you want it to go.

Now, go to sheet 2 and enter the text quot;2ACquot; (again without the quotes)
into cell F5. (We've chosen F5 only because it corresponds with your
original formula.)

Now in any OTHER cell, enter the following formula:
=VLOOKUP(F5,Sheet1!$A$1:$B$5,2,FALSE)

What you should see is the value 36320; that is, the return value for
the code 2AC.

The VLookup has 4 arguments, and what they mean is this:
WHAT value do you want to look up? Whatever's in cell F5.
WHERE do you want to look it up from? The table Sheet1!$A$1:$B$5.
WHICH column contains the value that we want to look up? Column 2.
DO we want an approximate match? No, which is why the last argument is
False.

In reality I wouldn't enter the lookup table into the VLookup formula
using absolute cell references like this, I'd use a range name. And a
dynamic range name at that. However taking it one step at a time, see
whether the above example makes it a bit clearer for you. If it does,
and you'd like to improve it by using range names, post again and
we'll take it from there.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

A table is simply two or more columns of data, as opposed to a list
which is just a single column. So a table of your data would look like:

1K 51126
2AC 36320
OAP 49003
3AC 36316
9CH 45284
1DB 318
6AC 53570
149 1045

In Sloth's posting, he was assuming that this table would be typed in
cells from A1 to B8, although you could put this table anywhere that is
suitable in your worksheet - if you do, you would need to change the
range A1:B8 in his formula to suit the cells where your table is
located.

Using a table like this, your long formula with all the IF statements
can be replaced with the VLOOKUP formula he supplied, i.e.:

=VLOOKUP(F5,A1:B8,2,FALSE)

What this does is to compare the value in cell F5 with the values in
the first column of the table, i.e. in column A, and if it finds an
exact match then it will return the corresponding value from column 2
of that table, i.e. from column B. This is exactly the same as having 8
IF statements. A further advantage of using Vlookup, though, is that
you can have many more entries in your table. Imagine you have 20
values of A and corresponding values in B, occupying A1 to B20 - all
you need to do is make a slight amendment to the formula as follows:

=VLOOKUP(F5,A1:B20,2,FALSE)

and this will accommodate the extra conditions.

If the value in F5 does not exist in the column of values in your
table, however, then the function will return #N/A to indicate this
error. Sloth's final formula helps you to prevent this from happening -
basically it means quot;If the lookup function will return an error, then
just put quot;quot; in the cell, otherwise let the lookup function return its
valuequot;.

Hope this helps to explain things a bit more.

Pete
I sure do appreciate all of the help. Especially Pete and Hank. You
guys just laid it out there and really took the time to help when you
did not have to; you did it cause that is the kind of guys you are, and
I really do appreciate that. Thank you.
It's working out well, except for the #N/A. I cant seem to get rid of
that. I am using the exact formula that was posted he

=VLOOKUP(F5,Sheet1!$A$2:$B$5,2,FALSE)

Any final suggestions would be appreciated.

Thanks again for helping a fella out.--
mcr1
------------------------------------------------------------------------
mcr1's Profile: www.excelforum.com/member.php...oamp;userid=15496
View this thread: www.excelforum.com/showthread...hreadid=498860
Judging by your if formula you should have at least 8 rows of data in
your table and your formula is looking at only 5 A2:B5, make sure that
the formula refers to the correct location of your table. The easiest
way to assure that is when your typing your formula, instead of typing
that part of your formula simply locate your table and select it, excel
will put the correct range in the formula.

HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=498860