Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex vlookup function - possible? help!
My boss asked me to help him figure out how to solve this problem. He
has one worksheet with many, many, many tables in it. Not one workBOOK, but one single worksheet that you scroll up, down, and side- to-side to see all of these tables. He has written formulas that identify the upper left and lower right cells of each table. For example, for the range below, one formula, let's call it "Formula 1" produce a result of "A1" in one cell which is the upper left cell in our table, and another formula, let's call it "Formula 2" produces "D4" in another cell which is the lower right cell in our table.: A B C D 1 2 3 4 He needs to do a vlookup that will use the results of Formula 1 and Formula 2, or integrate Formulas 1 and 2, within it to give him his answer. =vlookup(Q138,[needs to refer to cell with A1 result, or inetegrate formula that gives A1 result]:[needs to refer to cell with D4 result, or integrate formula that gives D4 result],3). Any suggestions? I'm not an advanced formula person (yet) and don't know VBA (yet), so I'm at a loss on this one. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex vlookup function - possible? help!
I think this is what you are looking for (no error handling in this formula
however, wouldn't want to give error handling without a better understanding of what is needed). =VLOOKUP(value,INDIRECT(formula1&":"&formula2),3,F ALSE) -- ** John C ** " wrote: My boss asked me to help him figure out how to solve this problem. He has one worksheet with many, many, many tables in it. Not one workBOOK, but one single worksheet that you scroll up, down, and side- to-side to see all of these tables. He has written formulas that identify the upper left and lower right cells of each table. For example, for the range below, one formula, let's call it "Formula 1" produce a result of "A1" in one cell which is the upper left cell in our table, and another formula, let's call it "Formula 2" produces "D4" in another cell which is the lower right cell in our table.: A B C D 1 2 3 4 He needs to do a vlookup that will use the results of Formula 1 and Formula 2, or integrate Formulas 1 and 2, within it to give him his answer. =vlookup(Q138,[needs to refer to cell with A1 result, or inetegrate formula that gives A1 result]:[needs to refer to cell with D4 result, or integrate formula that gives D4 result],3). Any suggestions? I'm not an advanced formula person (yet) and don't know VBA (yet), so I'm at a loss on this one. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex vlookup function - possible? help!
=VLOOKUP(Q138,INDIRECT(Cell1&":"&Cell2),.....etc
Where Cell1 and Cell2 are to be replaced with the cells references that contain begin and end cell -- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message ... My boss asked me to help him figure out how to solve this problem. He has one worksheet with many, many, many tables in it. Not one workBOOK, but one single worksheet that you scroll up, down, and side- to-side to see all of these tables. He has written formulas that identify the upper left and lower right cells of each table. For example, for the range below, one formula, let's call it "Formula 1" produce a result of "A1" in one cell which is the upper left cell in our table, and another formula, let's call it "Formula 2" produces "D4" in another cell which is the lower right cell in our table.: A B C D 1 2 3 4 He needs to do a vlookup that will use the results of Formula 1 and Formula 2, or integrate Formulas 1 and 2, within it to give him his answer. =vlookup(Q138,[needs to refer to cell with A1 result, or inetegrate formula that gives A1 result]:[needs to refer to cell with D4 result, or integrate formula that gives D4 result],3). Any suggestions? I'm not an advanced formula person (yet) and don't know VBA (yet), so I'm at a loss on this one. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with functionality more complex than VLOOKUP offers | Excel Worksheet Functions | |||
Complex Vlookup Table | Excel Discussion (Misc queries) | |||
Help with complex VLOOKUP | Excel Discussion (Misc queries) | |||
Help with complex VLOOKUP | Excel Worksheet Functions | |||
Complex VLOOKUP | Excel Discussion (Misc queries) |