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

#### gavinlim

##### New Member
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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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!

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

gavinlim said:
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.

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.

Replies
3
Views
2K
Replies
2
Views
379
Replies
3
Views
717
Replies
0
Views
340
Replies
12
Views
2K

1,203,213
Messages
6,054,200
Members
444,708
Latest member
David R__

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back