Hello,

I have a nested IF statement that needs to be much larger than it is.
Currently, there are six IF statements all nested into another IF. I need to
add eight more IF's into the formula, but Excel won't allow it. Below is the
formula. The formula does a HLookup at rangename AccountNumbers.

This is the formula as it is currently written:
=IF(H13="","",IF(A13="","",HLOOKUP(H13,AccountNumb ers,IF(A13="A",2,IF(A13="B",3,IF(A13="C",4,IF(A13= "D",5,IF(A13="E",6,""))))),FALSE)))

Any help anyone can offer would be great! Maybe there is another way of
doing this.
--
Ken

Hi,
you will not get it working with if statements, please post a sample of your
data and what you want to achieve and I will look for other solutions
"Ken" wrote:

Hello,

I have a nested IF statement that needs to be much larger than it is.
Currently, there are six IF statements all nested into another IF. I need to
add eight more IF's into the formula, but Excel won't allow it. Below is the
formula. The formula does a HLookup at rangename AccountNumbers.

This is the formula as it is currently written:
=IF(H13="","",IF(A13="","",HLOOKUP(H13,AccountNumb ers,IF(A13="A",2,IF(A13="B",3,IF(A13="C",4,IF(A13= "D",5,IF(A13="E",6,""))))),FALSE)))

Any help anyone can offer would be great! Maybe there is another way of
doing this.
--
Ken

Hi Eduardo,

I would like the account number in a cell (B2) to change based on the value
in an adjascent cell (B3) and the value of a 3rd cell (B1). So if (B3) =
CAR502, and (B1) is "C", the lookup finds that value (B2) in the table and
gives me the coorisponding value in row "C" of the table. The problem is
that i have 13 rows of data. The function works fine, except i have too many
rows to nest in the IF statement.

I hope this helps.
--
Ken


"Eduardo" wrote:


Hi,
you will not get it working with if statements, please post a sample of your
data and what you want to achieve and I will look for other solutions
"Ken" wrote:

Hello,

I have a nested IF statement that needs to be much larger than it is.
Currently, there are six IF statements all nested into another IF. I need to
add eight more IF's into the formula, but Excel won't allow it. Below is the
formula. The formula does a HLookup at rangename AccountNumbers.

This is the formula as it is currently written:
=IF(H13="","",IF(A13="","",HLOOKUP(H13,AccountNumb ers,IF(A13="A",2,IF(A13="B",3,IF(A13="C",4,IF(A13= "D",5,IF(A13="E",6,""))))),FALSE)))

Any help anyone can offer would be great! Maybe there is another way of
doing this.
--
Ken

Ok, in your example you give only 2 variables CAR502 and C, provide me with a
complete example, as per your spreadsheet

"Ken" wrote:

Hi Eduardo,

I would like the account number in a cell (B2) to change based on the value
in an adjascent cell (B3) and the value of a 3rd cell (B1). So if (B3) =
CAR502, and (B1) is "C", the lookup finds that value (B2) in the table and
gives me the coorisponding value in row "C" of the table. The problem is
that i have 13 rows of data. The function works fine, except i have too many
rows to nest in the IF statement.

I hope this helps.
--
Ken


"Eduardo" wrote:


Hi,
you will not get it working with if statements, please post a sample of your
data and what you want to achieve and I will look for other solutions
"Ken" wrote:

Hello,

I have a nested IF statement that needs to be much larger than it is.
Currently, there are six IF statements all nested into another IF. I need to
add eight more IF's into the formula, but Excel won't allow it. Below is the
formula. The formula does a HLookup at rangename AccountNumbers.

This is the formula as it is currently written:
=IF(H13="","",IF(A13="","",HLOOKUP(H13,AccountNumb ers,IF(A13="A",2,IF(A13="B",3,IF(A13="C",4,IF(A13= "D",5,IF(A13="E",6,""))))),FALSE)))

Any help anyone can offer would be great! Maybe there is another way of
doing this.
--
Ken

I assume you need to expand this portion:

IF(A13="A",2,IF(A13="B",3......

One way to do it would be to use another "lookup".

X1:X10 = letters A, B, C, D etc.

Then, you'd replace the nested if with something like this:

MATCH(A13,X1:X10,0)+1

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Hello,

I have a nested IF statement that needs to be much larger than it is.
Currently, there are six IF statements all nested into another IF. I need
to
add eight more IF's into the formula, but Excel won't allow it. Below is
the
formula. The formula does a HLookup at rangename AccountNumbers.

This is the formula as it is currently written:
=IF(H13="","",IF(A13="","",HLOOKUP(H13,AccountNumb ers,IF(A13="A",2,IF(A13="B",3,IF(A13="C",4,IF(A13= "D",5,IF(A13="E",6,""))))),FALSE)))

Any help anyone can offer would be great! Maybe there is another way of
doing this.
--
Ken