Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with functionality more complex than VLOOKUP offers cswale Excel Worksheet Functions 1 January 27th 06 10:49 AM
Complex Vlookup Table maacmaac Excel Discussion (Misc queries) 3 January 10th 06 12:11 PM
Help with complex VLOOKUP [email protected] Excel Discussion (Misc queries) 3 November 15th 05 08:43 PM
Help with complex VLOOKUP [email protected] Excel Worksheet Functions 3 November 15th 05 08:43 PM
Complex VLOOKUP Domenic Excel Discussion (Misc queries) 0 February 18th 05 07:37 PM


All times are GMT +1. The time now is 12:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"