Selecting Top Row/Left Column of Named Ranges; Index Match

[gavinlim]

Hi there

I've been using more and more named ranges within Index Match functions and have been generating formula such as:

=INDEX(ProfitImpact_Table,MATCH($A94,ProfitImpact_TableRows,0),MATCH(front!B$212,ProfitImpact_TableColumns,0))

where

ProfitImpact_Table refers to A1:G6
ProfitImpact_TableRows refers to A1:G1
ProfitImpact_TableColumns refers to A1:A6

All this works fine, but is there any way of referencing the first column within ProfitImpact_Table , or indeed the first row ?

I am finding myself creating 3 named ranges when in reality I am essentially referring to one, for Index Match.

I have seen some workaround, but they are so long and weighty that i am currently stuck with my method!

Any help would be great.

Thanks
Gavin

 

[gavinlim]

Re: Selecting Top Row/Left Column of Named Ranges; Index Mat

Apologies for the self-bump...

Is my question clear? sorry, i am really curious to see if i'm the only fool naming ranges three times when doing index matches

many thanks!

 

[Aladin Akyurek]

Re: Selecting Top Row/Left Column of Named Ranges; Index Mat

Hi there

I've been using more and more named ranges within Index Match functions and have been generating formula such as:

=INDEX(ProfitImpact_Table,MATCH($A94,ProfitImpact_TableRows,0),MATCH(front!B$212,ProfitImpact_TableColumns,0))

where

ProfitImpact_Table refers to A1:G6
ProfitImpact_TableRows refers to A1:G1
ProfitImpact_TableColumns refers to A1:A6

All this works fine, but is there any way of referencing the first column within ProfitImpact_Table , or indeed the first row ?

I am finding myself creating 3 named ranges when in reality I am essentially referring to one, for Index Match.

I have seen some workaround, but they are so long and weighty that i am currently stuck with my method!

Any help would be great.

Thanks
Gavin

ProfitImpact_TableRows and ProfitImpact_TableColumns are indeed not needed. So the formula you posted becomes, with PITable as shorthand for ProfitImpact_Table...

=INDEX(PITable,MATCH($A94,INDEX(PITable,0,1),0),MATCH(front!B$212,INDEX(PITable,1,0),0))

Note that there is also a drawback in having a definition like PITable which also includes headers: Some type of formulas would error uit because of the text-valued headers.

 

[gavinlim]

Re: Selecting Top Row/Left Column of Named Ranges; Index Mat

Wow. thank you very much. I was convinced there would be a short way.

And now i've spotted it actually described on the Help function! duh.