Index and Match functions I think

[Jim Butler]

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

 

[T. Valko]

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

 

[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]

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

 

[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]

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

 

[Jim Butler]

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]

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)

 

[Jim Butler]

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]

You're welcome. Thanks for the feedback!