can a lookup_array be described by a formula in INDEX/MATCH ?

[TomYC]

Instead of designating my lookup array with something such as
$A$10:$BA$10 in a particular cell, as part of an INDEX & MATCH combination (e.g., =VLOOKUP($A2,'Current Month'!$A$11:$BA$113,MATCH($A$1&" n",'Current Month'!$A$10:$BA$10,0),FALSE), I would like to be able to specify part of this with a formula, e.g. substitute =MATCH("QUESTION",'Current Month'!A:A,0) FOR $10 above, which might give me a complete formula something like:
=VLOOKUP($A2,'Current Month'!$A$11:$BA$113,MATCH($A$1&" n",'Current Month'!$A&MATCH("QUESTION",'Current Month'!A:A,0):$B&MATCH("QUESTION",'Current Month'!A:A,0),0),FALSE)
---of course, this syntax will not work but I hope it illustrates that what I am looking for is something like a dynamic range...

 

[SkipVought]

I use INDEX() & MATCH() exclusively, rather than VLOOKUP, because I either have 1) Named Ranges using the Heading values in the first row of the table, or 2) Structured Table, a feature for version 2007+ and thereby, use a COLUMN RANGE defined by a reference as just described for the Index range and then Match simply returns the row offset within the table.

So the form is typically...
[tt]
=INDEX(ColumnReturnRange,MATCH(LookupValue,LookupRange,0),1)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I failed to mention, that this method is generally superior, in that the lookup range can be ANY column within the table, and not the left-most, as is required by VLOOKUP.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

it's also quicker



...but I still use vlookups because it forces me to think about my primary keys for my data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gruuuu]

It sounds like you're looking for INDIRECT. You can do that. Look it up, you're pretty much there.

I, along with Skip, would recommend INDEX/MATCH. If you have dynamically changing ranges, OFFSET works easier with INDEX/MATCH, and also Tables work better with INDEX/MATCH. By the way, please use Tables (Insert->Table) on your data. It will save you headaches.

 

[SkipVought]

please use Tables (Insert->Table) on your data.

Structured Tables and Structured Tables References are a GREAT new advanced feature of Excel 2007+ -- well worth the time to research and get to know this valuable feature.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]