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.

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.

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.