Hello. I need help with what I thought was a simple "If, Then" type of
function. I would like to use functions to fill information in another cell.
My problem is that the first cell has a possibility of 12 different options
which would then result in a value. (So 12 options = 12 different values.)
Is this possible or can you tell me how to accompish this task?

Like all functions, IF allows up to 7 nesting levels. You could try a
'lookup' function (VLOOKUP, HLOOKUP)
Tell us more about the actual problem - please be concise!
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rlb" wrote in message
...
Hello. I need help with what I thought was a simple "If, Then" type of
function. I would like to use functions to fill information in another
cell.
My problem is that the first cell has a possibility of 12 different
options
which would then result in a value. (So 12 options = 12 different
values.)
Is this possible or can you tell me how to accompish this task?

Make a list of your 12 options in one out of the way column, then in the
next column to the right, put your corresponding values you want to show up
for each option.......say this is done in X1:Y12

Then if your "first" cell is A1, put this formula in B1

=VLOOKUP(A1,X1:Y12,2,FALSE)

Vaya con Dios,
Chuck, CABGx3


"rlb" wrote in message
...
Hello. I need help with what I thought was a simple "If, Then" type of
function. I would like to use functions to fill information in another
cell.
My problem is that the first cell has a possibility of 12 different
options
which would then result in a value. (So 12 options = 12 different
values.)
Is this possible or can you tell me how to accompish this task?

Use a lookup table

=VLOOKUP(A1, H1:L12,2,FALSE)

--
HTH

Bob Phillips

"rlb" wrote in message
...
Hello. I need help with what I thought was a simple "If, Then" type of
function. I would like to use functions to fill information in another
cell.
My problem is that the first cell has a possibility of 12 different
options
which would then result in a value. (So 12 options = 12 different
values.)
Is this possible or can you tell me how to accompish this task?

Hi Bernard,

I have many worksheets that use the same information. What I'd like to do
is assign a function so that when I type in (for example) EE-A in cell a1
that cell b1 would fill in with $295.92. This way I take the "human" factor
out of entering figures that I could transpose or accidentally fill in the
wrong figure. My problem is that I have 12 different scenarios where cell a1
could be EE-A or EE-B or EE-C or EE+Sp-A or EE+Sp-B or EE+Sp-C, etc etc.
Each of the 12 scenarios would need to produce their own specific dollar
amount. I know that with IF you can only assign 7 versions. Is there any
way to accomplish what I'm hoping for? It doesn't have to be an If, Then
statement. I just don't know enough about Excel to make this happen. Thanks
for your help!

"Bernard Liengme" wrote:

Like all functions, IF allows up to 7 nesting levels. You could try a
'lookup' function (VLOOKUP, HLOOKUP)
Tell us more about the actual problem - please be concise!
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rlb" wrote in message
...
Hello. I need help with what I thought was a simple "If, Then" type of
function. I would like to use functions to fill information in another
cell.
My problem is that the first cell has a possibility of 12 different
options
which would then result in a value. (So 12 options = 12 different
values.)
Is this possible or can you tell me how to accompish this task?

The VLOOKUP that has been referred to will allow you to have hundreds or
even thousands of values as in your example if you so wish. Rather more
efficient than trying to use nested IFs, and far easier to maintain as a
table of values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"lrb" wrote in message
...
Hi Bernard,

I have many worksheets that use the same information. What I'd like to do
is assign a function so that when I type in (for example) EE-A in cell a1
that cell b1 would fill in with $295.92. This way I take the "human"
factor
out of entering figures that I could transpose or accidentally fill in the
wrong figure. My problem is that I have 12 different scenarios where cell
a1
could be EE-A or EE-B or EE-C or EE+Sp-A or EE+Sp-B or EE+Sp-C, etc etc.
Each of the 12 scenarios would need to produce their own specific dollar
amount. I know that with IF you can only assign 7 versions. Is there any
way to accomplish what I'm hoping for? It doesn't have to be an If, Then
statement. I just don't know enough about Excel to make this happen.
Thanks
for your help!

"Bernard Liengme" wrote:

Like all functions, IF allows up to 7 nesting levels. You could try a
'lookup' function (VLOOKUP, HLOOKUP)
Tell us more about the actual problem - please be concise!
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rlb" wrote in message
...
Hello. I need help with what I thought was a simple "If, Then" type
of
function. I would like to use functions to fill information in
another
cell.
My problem is that the first cell has a possibility of 12 different
options
which would then result in a value. (So 12 options = 12 different
values.)
Is this possible or can you tell me how to accompish this task?

Ken,

I'm trying it now. Can you tell me if it's possible to "copy" and paste
special the format once I have it set up in one cell?

I'm trying, but keep getting errors.

Thanks

"Ken Wright" wrote:

The VLOOKUP that has been referred to will allow you to have hundreds or
even thousands of values as in your example if you so wish. Rather more
efficient than trying to use nested IFs, and far easier to maintain as a
table of values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"lrb" wrote in message
...
Hi Bernard,

I have many worksheets that use the same information. What I'd like to do
is assign a function so that when I type in (for example) EE-A in cell a1
that cell b1 would fill in with $295.92. This way I take the "human"
factor
out of entering figures that I could transpose or accidentally fill in the
wrong figure. My problem is that I have 12 different scenarios where cell
a1
could be EE-A or EE-B or EE-C or EE+Sp-A or EE+Sp-B or EE+Sp-C, etc etc.
Each of the 12 scenarios would need to produce their own specific dollar
amount. I know that with IF you can only assign 7 versions. Is there any
way to accomplish what I'm hoping for? It doesn't have to be an If, Then
statement. I just don't know enough about Excel to make this happen.
Thanks
for your help!

"Bernard Liengme" wrote:

Like all functions, IF allows up to 7 nesting levels. You could try a
'lookup' function (VLOOKUP, HLOOKUP)
Tell us more about the actual problem - please be concise!
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rlb" wrote in message
...
Hello. I need help with what I thought was a simple "If, Then" type
of
function. I would like to use functions to fill information in
another
cell.
My problem is that the first cell has a possibility of 12 different
options
which would then result in a value. (So 12 options = 12 different
values.)
Is this possible or can you tell me how to accompish this task?

"lrb" ha scritto nel messaggio


Hi Bernard,

I have many worksheets that use the same information. What I'd like
to do is assign a function so that when I type in (for example) EE-A
in cell a1 that cell b1 would fill in with $295.92. This way I take
the "human" factor out of entering figures that I could transpose or
accidentally fill in the wrong figure. My problem is that I have 12
different scenarios where cell a1 could be EE-A or EE-B or EE-C or
EE+Sp-A or EE+Sp-B or EE+Sp-C, etc etc. Each of the 12 scenarios
would need to produce their own specific dollar amount. I know that
with IF you can only assign 7 versions. Is there any way to
accomplish what I'm hoping for? It doesn't have to be an If, Then
statement. I just don't know enough about Excel to make this happen.
Thanks for your help!

"Bernard Liengme" wrote:

Like all functions, IF allows up to 7 nesting levels. You could try a
'lookup' function (VLOOKUP, HLOOKUP)
Tell us more about the actual problem - please be concise!
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rlb" wrote in message
...
Hello. I need help with what I thought was a simple "If, Then"
type of function. I would like to use functions to fill
information in another cell.
My problem is that the first cell has a possibility of 12 different
options
which would then result in a value. (So 12 options = 12 different
values.)
Is this possible or can you tell me how to accompish this task?


You can try this formula in B1:

=IF(OR(A1="a",A1="b",A1="c",A1="d",A1="e",A1="f"), IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,IF(A1="d",4,IF (A1="e",5,6))))),IF(OR(A1="g",A1="h",A1="i",A1="j" ,A1="k",A1="l"),IF(A1="g",7,IF(A1="h",8,IF(A1="i", 9,IF(A1="j",10,IF(A1="k",11,12))))),IF(A1="","","e rror")))

You have to substitute "a", "b", ..., "k" with your condition
("EE-A","EE-B",...) and the numbers 1, 2, 3, ... 12 with your values.

You can also use Data Validation on cell A1, with the option "choose from
list" and insert there your condition.

--
Hoping to be helpful...

Regards

Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

Got me confused on what you are trying to do there.

Here is a previous attempt at an explanation of how VLOOKUP works that is
hopefully a bit easier than the help option. Note, you should also look at
Data Validation for entering your codes given that you have a discrete set
of values for them, but lets crack the VLOOKUP bit first :-)

The VLOOKUP function essentially takes a value that you specify, whether it
be a hardcoded
number/letter/text or a value within a cell reference, and then goes and
looks it up in a table.
It will look for that value in the leftmost column of the table, and either
find it or the closest
match, and will then return the corresponding value on the same row, in
whatever column of that
table that you tell it to:-

Example - With the following table

D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40

and with your value that you are looking up in say cell A1 (and let's assume
it is 25 for
example).

You can put a formula in pretty much any other cell, that says, take the
value in cell A1 (25), go
and look for it in the lefthand column of the table (D1:D5), and then when
you have found it (or
the lowest closest number to it), go to the second column (or whichever one
you specify if there
are more than 2) and give me the number that it is on the same row in that
column.

So, with the formula being =VLOOKUP(A1,D1:E5,2) it will first take the
value in A1 which is 25,
then go look for it in the leftmost column (D) of your specified table
(D1:E5), and it will try to
find that number. Now it isn't there, so what it will do is look for the
next lower closest
number, which in this case will be 20, and the 2 in the formula says to go
and get the value in
the 2nd column (E) in your table, that is on the same row as the 20. That
value in this case is
0.3

If you put the value 30 or 31 or 32 etc into A1 now, you will see the result
of the formula
change, because now it will either find those numbers or the lower closest
number (and in each
case there it is 30), and will subsequently return 0.35 as the corresponding
value.

Just to show you how the 2 really works in that formula, if you added one
more column to your
table so that it looked like this:-

D E F
1 1 0.20 0.15
2 10 0.25 0.25
3 20 0.30 0.35
4 30 0.35 0.45
5 40 0.40 0.55

and you actually wanted the value from Col F, then you would simpl;y change
the 2 in the formula
to a 3 to signify the third column, eg:-

=VLOOKUP(A1,D1:E5,3)

With the examples already given, 25 in A1 would return 0.35, and 30/31/32
would return 0.45

The one caveat to all of this (When getting the nearest number is OK) is
that the data in your
leftmost column must be sorted in ascending order.


There are times when you would only want it to give you a value if you had
an exact match on the
number, and in this instance you would simply add a 4th argument of 0 or
FALSE to the formula,
eg:-

=VLOOKUP(A1,D1:E5,3,0)

or

=VLOOKUP(A1,D1:E5,3,FALSE)

In these cases you do not need to have the data in your leftmost column
sorted.




--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

snip