Range Name reference from a value in a cell 1

[ebrooks54]

This may be about the most complicated formula that I have come up with as an amateur, but I am afraid it needs to be a little bit more complicated abd I can't figure out these last two steps:

=INDEX(OFFSET(A1A5E8,0,4,44,1),MATCH(F7,OFFSET(A1A5E8,0,2,44,1),-1))

A1A5E8 (which is a Canadian Postal Code) is a named range, in this case referencing cells A2:E45. The formula above works fine and gives the expected results.

The contents of G7 is 'A1A5E8. I want to change the above formula so that named range is picked up from the value in G7 instead of being hard-coded in the formula.

Lastly, the number 44 in the formula is hard-coded to be the number of rows in the named range. I would like to change that to a calculated value of the number of rows in the range.

Would appreciate some ideas...

Thanks

(Excel Version is XL2003)

 

[SkipVought]

[tt]
=INDEX(OFFSET(INDIRECT(A1A5E8),0,4,44,1),MATCH(F7,OFFSET(A1A5E8,0,2,44,1),-1))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Its really a better practice, IMHO, to reference the TOP LEFT cell of your range in the first OFFSET Argument...
[tt]
=INDEX(OFFSET(TopLeftCell,0,4,44,1),MATCH(F7,OFFSET(TopLeftCell,SomeMATCH,2,44,1),-1))
[/tt]
I would expect CanadianPostalCodes to be the named range and NOT individual Postal Codes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ebrooks54]

Skip,

Maybe I have expressed myself poorly, but this fails in two ways.

The replacement for the named range "A1A5E8" in my formula has to reference the value in G7. If I just make the change as you suggest, the result is #Value!.

In terms of the construct I am creating, there are a series of ranges that are named with individual postal codes.

For example

PCode1 is 44 rows by 5 columns
Pcode2 is 20 rows by 5 columns
pcode3 is 35 rows by 5 columns

The formula I am trying to write is looking for a value based on another value in the table for the corresponding postal code, which in the results area can vary line by line. For instance, the value in cell G7 is "PCODE1", in G8 it is "PCODE3", and G9 is "PCODE2"

To work with a single range named "CanPostCode", I would essentially have to do some kind of double lookup that is beyond my skill. What I have is a postal code and a weight. Based on the postal code and weight, I have varying rates. The formula is kind of a backward vlook-up function, in that it returns the next highest value instead of the lower.

Thanks

 

[SkipVought]

INDIRECT does work! Are you using it correctly?
[tt]
=INDEX(OFFSET(A1A5E8,0,4,44,1),MATCH(F7,OFFSET(A1A5E8,0,2,44,1),-1))
[/tt]
Right off, I see 44 as the ROW COUNT. You could be using ROWS(INDIRECT(YourRangeName)) to return the number of rows

Maybe you could post some sample data (5 columns ? rows)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ebrooks54]

Skip,

My apologies. What you suggested did work; brilliantly. I had a typo somewhere in the convoluted statement, which now reads:

=INDEX(OFFSET(INDIRECT(G7),0,4,ROWS(INDIRECT(G7)),1),MATCH(F7,OFFSET(INDIRECT(G7),0,2,ROWS(INDIRECT(G7)),1),-1))

Many, many thanks for your help.