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="1K","51126",IF(F5="2AC","36320",IF(F5="0AP","49003",IF(F5="3AC","36316",IF(F5="9CH","45284",IF(F5="1DB","318",IF(F5="6AC","53570",IF(F5="149","1045",""))))))))
I am wanting to be able to put as many as I want. Thanks in advance. Mcr1
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
On Fri, 6 Jan 2006 15:14:48 -0600, mcr1
<[email protected]> wrote:
>
>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="1K","51126",IF(F5="2AC","36320",IF(F5="0AP","49003",IF(F5="3AC","36316",IF(F5="9CH","45284",IF(F5="1DB","318",IF(F5="6AC","53570",IF(F5="149","1045",""))))))))
>
>I am wanting to be able to put as many as I want. Thanks in advance.
What you're doing is called "nesting", and it has a limit.
Specifically:
"Nesting level limits: A formula can contain up to seven levels of
nested functions."
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! *
Excel 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,{"1K","51126";"2AC","36320";"0AP","49003";"3AC","36316";"9CH","45284";"1DB","318";"6AC","53570";"149","1045"},2,0)
and if you want the "" part you need something like this...
=IF(ISERROR(VLOOKUP(F5,{"1K","51126";"2AC","36320";"0AP","49003";"3AC","36316";"9CH","45284";"1DB","318";"6AC","53570";"149","1045"},2,0)),"",VLOOKUP(F5,{"1K","51126";"2AC","36320";"0AP","49003";"3AC","36316";"9CH","45284";"1DB","318";"6AC","53570";"149","1045"},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 "" part
=IF(ISERROR(VLOOKUP(F5,A1:B8,2,FALSE)),"",VLOOKUP(F5,A1:B8,2,FALSE))
"mcr1" wrote:
>
> 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="1K","51126",IF(F5="2AC","36320",IF(F5="0AP","49003",IF(F5="3AC","36316",IF(F5="9CH","45284",IF(F5="1DB","318",IF(F5="6AC","53570",IF(F5="149","1045",""))))))))
>
> I am wanting to be able to put as many as I want. Thanks in advance.
> Mcr1
>
>
> --
> mcr1
> ------------------------------------------------------------------------
> mcr1's Profile: http://www.excelforum.com/member.php...o&userid=15496
> View this thread: http://www.excelforum.com/showthread...hreadid=498860
>
>
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
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 "Names in workbook" 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
On Fri, 6 Jan 2006 17:37:03 -0600, mcr1
<[email protected]> wrote:
>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
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 "Cell Value". (Without the quotes)
In cell B1, enter "Lookup value". 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 -> 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 "2AC" (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 "If the lookup function will return an error, then
just put "" in the cell, otherwise let the lookup function return its
value".
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 here:
=VLOOKUP(F5,Sheet1!$A$2:$B$5,2,FALSE)
Any final suggestions would be appreciated.
Thanks again for helping a fella out.
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
On Fri, 6 Jan 2006 21:20:11 -0600, mcr1
<[email protected]> wrote:
>
>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.
You're welcome.
>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 here:
>
>=VLOOKUP(F5,Sheet1!$A$2:$B$5,2,FALSE)
OK, I didn't cover N/A errors in my first post because I thought it
more important that you get a handle on the structure of the table,
and on using the VLookup function. Pete did cover it in in his post to
some extent.
You'll get the N/A error if the value that you're looking up (that is,
the first argument of the Vlookup formula) doesn't appear in the first
column of the table.
There could be a number of reasons for this.
- The first is (obviously) that the value that you're looking up
just doesn't appear in the first column.
- The second is if the value that you're looking up is stored as a
number in your table, but you're looking it up as text or vice versa.
(That could be a bit confusing. Let me explain it this way; if the
formula was, say, =VLOOKUP(75,Sheet1!$A$1:$B$7,2,FALSE), then the
NUMBER 75 must appear somewhere in the first column of the lookup
table. If instead you have 75 *formatted as text* in that table,
VLookup won't find it and will return N/A. Conversely, if you have the
function as =VLOOKUP("75",Sheet1!$A$1:$B$7,2,FALSE) (note the double
quotes around the 75 this time) and you have 75 formatted as a NUMBER
in the lookup table, again, Vlookup won't find it. I don't think this
applies in your case (so far) because all of your first column entries
appear to be alphanumeric (that is, they have both text AND numbers),
so they would have to be formatted as text. I'm only mentioning it in
case you come across this problem in the future.)
- The third possibility, which was mentioned by JG/Pinmaster, is if
the row containing the lookup value falls OUTSIDE the range that
you've specified as the table. I listed only the first 5 of your
entries and then said that you could spread it down as far as you
needed to. Pete was less lazy than me and specified all of the
combinations that you had mentioned in your original post. But in any
case, you DO need to make sure that the range name $A$2:$B$5 is
changed as needed to specify the WHOLE list. If you have 7 items,
it'll be $A$2:$B$8. If you have 10, it'll be $A$2:$B$11 and so on. (In
fact I'd usually start from row 1, not 2, even if the first row
contains headings only. That way if you insert a new row at the top of
the table (that is, in row 2), the VLookup formulas will automatically
change to include the new row.)
What people normally do when using a VLookup is to use an IsError or
IsNa function as well to prevent the N/A error from appearing. For
example:
=IF(ISNA(VLOOKUP(F5,Sheet1!$A$1:$B$7,2,FALSE)),"Not Found",
VLOOKUP(F5,Sheet1!$A$1:$B$7,2,FALSE))
That way it will show a more informative error message if, for
whatever reason, the value isn't there.
>Any final suggestions would be appreciated.
>
>Thanks again for helping a fella out.
---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks