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!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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,201
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,840
Messages
5,598,386
Members
414,234
Latest member
grlevesq

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