I have a very large and complex formula and am currently using the "IF" "AND"
functions. But with the limitation of 7 "IF" functions I think there has to
be a better way. Column "D" and "E" both have numeric data that needs to
reference to a table and output that table number to column "F" so that it
can be multiplied and square rooted by other cells to give another answer
into another cell. I do have it working with the IF AND functions but as I
said it is very limited. Any push in the right direction would surely be
appreciated. I can copy and paste some of the data here if that would make it
any easier.

Thank you,
Jim Butler

In other words, you want to search for a value along a vertical axis then
lookup another value along a horizontal axis and locate the value at the
intersection?

Ok, that's easy enough........but.......the exact formula to use depends on
the application. See if these help:

http://contextures.com/xlFunctions02.html

http://contextures.com/xlFunctions03.html


--
Biff
Microsoft Excel MVP


"Jim Butler" <Jim wrote in message
...
I have a very large and complex formula and am currently using the "IF"
"AND"
functions. But with the limitation of 7 "IF" functions I think there has
to
be a better way. Column "D" and "E" both have numeric data that needs to
reference to a table and output that table number to column "F" so that it
can be multiplied and square rooted by other cells to give another answer
into another cell. I do have it working with the IF AND functions but as I
said it is very limited. Any push in the right direction would surely be
appreciated. I can copy and paste some of the data here if that would make
it
any easier.

Thank you,
Jim Butler

Thank you for the quick reply. From everything that I have tried I don't
think that either vlookup or hlookup will work. Have been trying to use index
and match to no avail.
This is one of the formulas that I am using right now and it is not large
enough because of nesting limitations:
=IF(AND(D2=2,D2<=3),IF(E2=0.375,"28.427",IF(E2=0. 25,"12.711",IF(E2=0.5,"50.523",IF(E2=0.625,"79.054 ",IF(E2=0.75,"115.143",IF(E2=0.875,"158.48",IF(E2= 1,"210.23"))))))))
Then the number displayed gets manipulated by another formula. The numbers
in the quotations come from a table (or what I refer to as a table) that has
all the numbers that E2 references to down one column and the numbers that D2
references to across on each corresponding row. sure hope that makes sense to
you.

Thank you,
Jim


"T. Valko" wrote:

In other words, you want to search for a value along a vertical axis then
lookup another value along a horizontal axis and locate the value at the
intersection?

Ok, that's easy enough........but.......the exact formula to use depends on
the application. See if these help:

http://contextures.com/xlFunctions02.html

http://contextures.com/xlFunctions03.html


--
Biff
Microsoft Excel MVP


"Jim Butler" <Jim wrote in message
...
I have a very large and complex formula and am currently using the "IF"
"AND"
functions. But with the limitation of 7 "IF" functions I think there has
to
be a better way. Column "D" and "E" both have numeric data that needs to
reference to a table and output that table number to column "F" so that it
can be multiplied and square rooted by other cells to give another answer
into another cell. I do have it working with the IF AND functions but as I
said it is very limited. Any push in the right direction would surely be
appreciated. I can copy and paste some of the data here if that would make
it
any easier.

Thank you,
Jim Butler

the numbers that D2 references to across
on each corresponding row.

What are those numbers and where are they located?

Is this what your table looks like:

................2.0.....2.5.....3.0
0.250.......5........3........2
0.375.......1........7........4
0.500.......2........5........3
0.625.......2........4........6
0.750.......3........3........7
0.875.......4........1........2
1.000.......5........7........9


--
Biff
Microsoft Excel MVP


"Jim Butler" wrote in message
...
Thank you for the quick reply. From everything that I have tried I don't
think that either vlookup or hlookup will work. Have been trying to use
index
and match to no avail.
This is one of the formulas that I am using right now and it is not large
enough because of nesting limitations:
=IF(AND(D2=2,D2<=3),IF(E2=0.375,"28.427",IF(E2=0. 25,"12.711",IF(E2=0.5,"50.523",IF(E2=0.625,"79.054 ",IF(E2=0.75,"115.143",IF(E2=0.875,"158.48",IF(E2= 1,"210.23"))))))))
Then the number displayed gets manipulated by another formula. The numbers
in the quotations come from a table (or what I refer to as a table) that
has
all the numbers that E2 references to down one column and the numbers that
D2
references to across on each corresponding row. sure hope that makes sense
to
you.

Thank you,
Jim


"T. Valko" wrote:

In other words, you want to search for a value along a vertical axis then
lookup another value along a horizontal axis and locate the value at the
intersection?

Ok, that's easy enough........but.......the exact formula to use depends
on
the application. See if these help:

http://contextures.com/xlFunctions02.html

http://contextures.com/xlFunctions03.html


--
Biff
Microsoft Excel MVP


"Jim Butler" <Jim wrote in message
...
I have a very large and complex formula and am currently using the "IF"
"AND"
functions. But with the limitation of 7 "IF" functions I think there
has
to
be a better way. Column "D" and "E" both have numeric data that needs
to
reference to a table and output that table number to column "F" so that
it
can be multiplied and square rooted by other cells to give another
answer
into another cell. I do have it working with the IF AND functions but
as I
said it is very limited. Any push in the right direction would surely
be
appreciated. I can copy and paste some of the data here if that would
make
it
any easier.

Thank you,
Jim Butler

1.687 1.939 2.067 2.3 2.624
0.250 12.696 12.708 12.711 12.714 12.712
0.375 28.475 28.44 28.428 28.411 28.394
0.500 50.78 50.588 50.523 50.436 50.358
0.625 80.099 79.51 79.313 79.054 78.82
0.750 117.119 115.62 115.143 114.524 113.99
0.875 162.99 159.56 158.48 157.13 156.01
1.000 219.86 212.47 210.23 207.44 205.19
This is what is looks like (part of it to save space). I have not defined it
as a table. The top row is what D2 references to as I put them in manually
and down the first column is what E2 references to as I put it in manually.
Would like the formula to reference to the proper number corresponding to
i.e: D2=2.067 and then E2=.625 and putting the corresponding number of 79.313
in F2.



"T. Valko" wrote:

the numbers that D2 references to across
on each corresponding row.

What are those numbers and where are they located?

Is this what your table looks like:

................2.0.....2.5.....3.0
0.250.......5........3........2
0.375.......1........7........4
0.500.......2........5........3
0.625.......2........4........6
0.750.......3........3........7
0.875.......4........1........2
1.000.......5........7........9


--
Biff
Microsoft Excel MVP


"Jim Butler" wrote in message
...
Thank you for the quick reply. From everything that I have tried I don't
think that either vlookup or hlookup will work. Have been trying to use
index
and match to no avail.
This is one of the formulas that I am using right now and it is not large
enough because of nesting limitations:
=IF(AND(D2=2,D2<=3),IF(E2=0.375,"28.427",IF(E2=0. 25,"12.711",IF(E2=0.5,"50.523",IF(E2=0.625,"79.054 ",IF(E2=0.75,"115.143",IF(E2=0.875,"158.48",IF(E2= 1,"210.23"))))))))
Then the number displayed gets manipulated by another formula. The numbers
in the quotations come from a table (or what I refer to as a table) that
has
all the numbers that E2 references to down one column and the numbers that
D2
references to across on each corresponding row. sure hope that makes sense
to
you.

Thank you,
Jim


"T. Valko" wrote:

In other words, you want to search for a value along a vertical axis then
lookup another value along a horizontal axis and locate the value at the
intersection?

Ok, that's easy enough........but.......the exact formula to use depends
on
the application. See if these help:

http://contextures.com/xlFunctions02.html

http://contextures.com/xlFunctions03.html


--
Biff
Microsoft Excel MVP


"Jim Butler" <Jim wrote in message
...
I have a very large and complex formula and am currently using the "IF"
"AND"
functions. But with the limitation of 7 "IF" functions I think there
has
to
be a better way. Column "D" and "E" both have numeric data that needs
to
reference to a table and output that table number to column "F" so that
it
can be multiplied and square rooted by other cells to give another
answer
into another cell. I do have it working with the IF AND functions but
as I
said it is very limited. Any push in the right direction would surely
be
appreciated. I can copy and paste some of the data here if that would
make
it
any easier.

Thank you,
Jim Butler

Let's assume your table is in the range H2:M9...

D2 = 2.067
E2 = 0.625

=VLOOKUP(E2,H2:M9,MATCH(D2,H2:M2,0),0)

Returns: 79.313

--
Biff
Microsoft Excel MVP


"Jim Butler" wrote in message
...
1.687 1.939 2.067 2.3 2.624
0.250 12.696 12.708 12.711 12.714 12.712
0.375 28.475 28.44 28.428 28.411 28.394
0.500 50.78 50.588 50.523 50.436 50.358
0.625 80.099 79.51 79.313 79.054 78.82
0.750 117.119 115.62 115.143 114.524 113.99
0.875 162.99 159.56 158.48 157.13 156.01
1.000 219.86 212.47 210.23 207.44 205.19
This is what is looks like (part of it to save space). I have not defined
it
as a table. The top row is what D2 references to as I put them in manually
and down the first column is what E2 references to as I put it in
manually.
Would like the formula to reference to the proper number corresponding to
i.e: D2=2.067 and then E2=.625 and putting the corresponding number of
79.313
in F2.



"T. Valko" wrote:

the numbers that D2 references to across
on each corresponding row.

What are those numbers and where are they located?

Is this what your table looks like:

................2.0.....2.5.....3.0
0.250.......5........3........2
0.375.......1........7........4
0.500.......2........5........3
0.625.......2........4........6
0.750.......3........3........7
0.875.......4........1........2
1.000.......5........7........9


--
Biff
Microsoft Excel MVP


"Jim Butler" wrote in message
...
Thank you for the quick reply. From everything that I have tried I
don't
think that either vlookup or hlookup will work. Have been trying to use
index
and match to no avail.
This is one of the formulas that I am using right now and it is not
large
enough because of nesting limitations:
=IF(AND(D2=2,D2<=3),IF(E2=0.375,"28.427",IF(E2=0. 25,"12.711",IF(E2=0.5,"50.523",IF(E2=0.625,"79.054 ",IF(E2=0.75,"115.143",IF(E2=0.875,"158.48",IF(E2= 1,"210.23"))))))))
Then the number displayed gets manipulated by another formula. The
numbers
in the quotations come from a table (or what I refer to as a table)
that
has
all the numbers that E2 references to down one column and the numbers
that
D2
references to across on each corresponding row. sure hope that makes
sense
to
you.

Thank you,
Jim


"T. Valko" wrote:

In other words, you want to search for a value along a vertical axis
then
lookup another value along a horizontal axis and locate the value at
the
intersection?

Ok, that's easy enough........but.......the exact formula to use
depends
on
the application. See if these help:

http://contextures.com/xlFunctions02.html

http://contextures.com/xlFunctions03.html


--
Biff
Microsoft Excel MVP


"Jim Butler" <Jim wrote in message
...
I have a very large and complex formula and am currently using the
"IF"
"AND"
functions. But with the limitation of 7 "IF" functions I think there
has
to
be a better way. Column "D" and "E" both have numeric data that
needs
to
reference to a table and output that table number to column "F" so
that
it
can be multiplied and square rooted by other cells to give another
answer
into another cell. I do have it working with the IF AND functions
but
as I
said it is very limited. Any push in the right direction would
surely
be
appreciated. I can copy and paste some of the data here if that
would
make
it
any easier.

Thank you,
Jim Butler