Finding Named Range Name Based on a cell found within that range.

**DonkeyOte** · 10-13-2009, 04:21 PM

Yes, I gather the header text is in F but the key is - is there logic by which the header rows can be determined (other than that they are the first row of the named range)...

For sake of illustration of my thinking... let's "assume" F is blank other than for header rows... you could achieve your results using formulae as so

=LOOKUP(REPT("Z",255),F1:INDEX(F:F,ROW(INDIRECT(M1))))

the above would find the last value in F up to an including the row as determined by range reference in M1.

Now in reality the above logic may not be in place but there may be other logical rules by which we can determine as and where the header rows are located.

**Sean Mc** · 10-13-2009, 08:56 PM

Hi again Don,

The only repeating pattern is that column "A" in the Range Header Row is always empty, and it is the only place that cell "A" is empty.
Ill put some thought into what you've given me so far.

Thanks
~ Sean

**DonkeyOte** · 10-14-2009, 03:01 AM

The only repeating pattern is that column "A" in the Range Header Row is always empty, and it is the only place that cell "A" is empty.

so on that basis perhaps

=LOOKUP(2,1/(A1:INDEX(A:A,ROW(INDIRECT(M1)))=""),F1:INDEX(F:F,ROW(INDIRECT(M1))))

It would be better to adjust M1 such that you don't need to use INDIRECT but without knowing how that's determined it's hard to offer further insight at this stage.