Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 24th 08, 02:14 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 1
Default Index and Match functions I think

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

  #2   Report Post  
Old November 24th 08, 03:02 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Index and Match functions I think

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



  #3   Report Post  
Old November 24th 08, 04:24 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 4
Default Index and Match functions I think

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




  #4   Report Post  
Old November 24th 08, 04:58 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Index and Match functions I think

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






  #5   Report Post  
Old November 24th 08, 06:15 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 4
Default Index and Match functions I think

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








  #6   Report Post  
Old November 24th 08, 07:04 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Index and Match functions I think

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








  #7   Report Post  
Old November 24th 08, 01:09 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 4
Default Index and Match functions I think

Thank you, that definitely works better than what I have been doing so far.
Still not complete though. The cell D2 references to greater than and/or less
than, which is not a problem as I just revamped the table to allow for it,
but when I do an auto fill it changes the reference of H2:M2 to H3...:M3...
and so on down the line. Which I could live with except that it has over
1,000 rows that I will have to edit after autofill.
Is there a way to autofill and not change the match number range?? Doesn't
sound doable, but had to ask..


"T. Valko" wrote:

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









  #8   Report Post  
Old November 24th 08, 05:18 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Index and Match functions I think

Not sure I follow you on this:

The cell D2 references to greater than and/or less than


To stop the references from changing when you copy the formula, make them
absolute:

=VLOOKUP(E2,$H$2:$M$9,MATCH(D2,$H$2:$M$2,0),0)


--
Biff
Microsoft Excel MVP


"Jim Butler" wrote in message
...
Thank you, that definitely works better than what I have been doing so
far.
Still not complete though. The cell D2 references to greater than and/or
less
than, which is not a problem as I just revamped the table to allow for it,
but when I do an auto fill it changes the reference of H2:M2 to
H3...:M3...
and so on down the line. Which I could live with except that it has over
1,000 rows that I will have to edit after autofill.
Is there a way to autofill and not change the match number range?? Doesn't
sound doable, but had to ask..


"T. Valko" wrote:

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











  #9   Report Post  
Old November 24th 08, 10:50 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 4
Default Index and Match functions I think

Thank you for all your help, do not know why I take something that is so easy
and make it so hard. Guess that is why the experts like you are here........



"T. Valko" wrote:

Not sure I follow you on this:

The cell D2 references to greater than and/or less than


To stop the references from changing when you copy the formula, make them
absolute:

=VLOOKUP(E2,$H$2:$M$9,MATCH(D2,$H$2:$M$2,0),0)


--
Biff
Microsoft Excel MVP


"Jim Butler" wrote in message
...
Thank you, that definitely works better than what I have been doing so
far.
Still not complete though. The cell D2 references to greater than and/or
less
than, which is not a problem as I just revamped the table to allow for it,
but when I do an auto fill it changes the reference of H2:M2 to
H3...:M3...
and so on down the line. Which I could live with except that it has over
1,000 rows that I will have to edit after autofill.
Is there a way to autofill and not change the match number range?? Doesn't
sound doable, but had to ask..


"T. Valko" wrote:

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












  #10   Report Post  
Old November 25th 08, 02:34 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Index and Match functions I think

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jim Butler" wrote in message
...
Thank you for all your help, do not know why I take something that is so
easy
and make it so hard. Guess that is why the experts like you are
here........



"T. Valko" wrote:

Not sure I follow you on this:

The cell D2 references to greater than and/or less than


To stop the references from changing when you copy the formula, make them
absolute:

=VLOOKUP(E2,$H$2:$M$9,MATCH(D2,$H$2:$M$2,0),0)


--
Biff
Microsoft Excel MVP


"Jim Butler" wrote in message
...
Thank you, that definitely works better than what I have been doing so
far.
Still not complete though. The cell D2 references to greater than
and/or
less
than, which is not a problem as I just revamped the table to allow for
it,
but when I do an auto fill it changes the reference of H2:M2 to
H3...:M3...
and so on down the line. Which I could live with except that it has
over
1,000 rows that I will have to edit after autofill.
Is there a way to autofill and not change the match number range??
Doesn't
sound doable, but had to ask..


"T. Valko" wrote:

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
















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Row, Index, Match functions Christopher Naveen[_2_] Excel Discussion (Misc queries) 1 September 20th 07 08:42 AM
INDEX & MATCH functions Iriemon Excel Worksheet Functions 2 August 28th 07 03:50 PM
Index and Match Functions Damien Excel Worksheet Functions 4 July 29th 06 10:12 PM
Help Please - Match & Index Functions (I hope)! baz Excel Discussion (Misc queries) 0 September 2nd 05 02:42 PM
How do I use the Match and Index functions to look up a value tha. Maclanders Excel Worksheet Functions 7 February 10th 05 08:31 PM


All times are GMT +1. The time now is 09:56 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017