Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row, Index, Match functions | Excel Discussion (Misc queries) | |||
INDEX & MATCH functions | Excel Worksheet Functions | |||
Index and Match Functions | Excel Worksheet Functions | |||
Help Please - Match & Index Functions (I hope)! | Excel Discussion (Misc queries) | |||
How do I use the Match and Index functions to look up a value tha. | Excel Worksheet Functions |