Table/Name/Index Help

Tamm501

New Member
Joined
Aug 15, 2007
Messages
7
I have a table that has 2 rows of information for each month. Using the ctrl key, I selected the alternating rows and named the entire selection.

I am using index/match formula and I have inserted the named region in the match portion of the formula. However, I keep getting an N/A. Can you not use named regions isf the rows do not immediately follow?

Named region = test

=Index(Stats!b1:b19,match(a2,test,0))

Any ideas on what I might try?

Thanks!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
What is the range that corresponds to 'test'?

test range:

=Stats!$A$7,Stats!$A,$9,etc......

Your setup is unusual. If you don't want to reconsider it, try if the following helps...

Code:
=INDEX(Stats!$A$7:$A$41,
    IF(MATCH(A2,Stats!$B$1:$B$19,0)>1,
        2*MATCH(A2,Stats!$B$1:$B$19,0)-1,
        1))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
From a PM:

Hi Aladin,

I appreciate you answering my post earlier on today. I am somewhat new to indexing and if formulas and I am still having difficulty with your suggestion and wonder if you would be able to help me further. I pasted in the tabs and the formula I used for the formula for Aug and it is not working properly.

I am not sure what the 2*Match portion of the formula or the -1 at the end is doing so if you could explain that I might be able to figure this out. Any help is greatly appreciated!

Stat tab
A B C
July EP 10
PM 100
Aug EP 20
PM 200
Sep EP 30
PM 300
Oct EP 40
PM 400


Sheet 2
A B C D A
July Aug Sep Oct
EP 10 20 30 40
PM 100 300 0 0
s/b 200 in Aug.
Formula in C3:
=INDEX(Stats!$C$1:$C$20,IF(MATCH(Sheet2!C2,Stats!$A$1:$A$20,0)>1,2*MATCH(Sheet2!C2,Stats!$A$1:$A$20,0)-1,1))


Change your setup on Stats from
Book1
ABCD
2JulyEP10
3PM100
4AugEP20
5PM200
6SepEP30
7PM300
8OctEP40
9PM400
Stats


to:
Book1
ABCD
1EPPM
2Jul10100
3Aug20200
4Sep30300
5Oct40400
6
Stats


On Sheet2...
Book1
ABCDE
1JulAugSepOct
2EP10203040
3PM100200300400
4
5
Sheet2


B2, copied across and down:

=INDEX(Stats!$B$2:$C$5,MATCH(B$1,Stats!$A$2:$A$5,0),MATCH($A2,Stats!$B$1:$C$1,0))
 

Tamm501

New Member
Joined
Aug 15, 2007
Messages
7
I was afraid it would require a table change. I work in a system office and sometimes the departments are resistant to change. I am going to have to convince them this is the best way! Wish me luck :biggrin:

Thanks for the advice/instruction. I have learned so much here in the past few days and I appreciate everyone who posts solutions more than you know!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,468
Messages
5,831,809
Members
430,087
Latest member
meagerd

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top