View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.newusers
Jim Butler[_2_] Jim Butler[_2_] is offline
external usenet poster
 
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