Help with Index & Match

[themev]

I have a file with dates in column A running from Rows 4:260 and the 50 states as columns in Row 2 across columns B:BA

ROW 2	Alabama	Arizona...	Wyoming
ROW 4, COLUMN A January 2001
....
(A260) May 2022	$2.22	$2.54	$4.64

I am trying to pull data for dates in a different table:

Cell A266 = May 2022

Cell B265 = Alabama

In Cell B266 I put in the formula =INDEX($A$2:$BA$260,MATCH($A266,$A$4:$A$260,0),MATCH($B$265,$B$2:$BA$2,0))
but I am not getting it to return $2.22 which is what I want...I can't even figure out where the number it is pulling is coming from.
What am I doing wrong here?
Thanks for the help!

 

[etaf]

Are your dates - real dates or TEXT ??
should work - but INDEX() should be the data range


so B2:Ba260 - not A2 - which is the dates

heres a sample of what i think you are after - but in columns A and row 2 for simplicity

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		Alabama	Alaska	Arizona	Arkansas	California	Colorado	Connecticut	Delaware	Florida	Georgia	Hawaii	Idaho	Illinois
3	1/1/22	1	2	3	4	5	6	7	8	9	10	11	12	13
4	1/2/22	14	15	16	17	18	19	20	21	22	23	24	25	26
5	1/3/22	27	28	29	30	31	32	33	34	35	36	37	38	39
6	1/4/22	40	41	42	43	44	45	46	47	48	49	50	51	52
7	1/5/22	53	54	55	56	57	58	59	60	61	62	63	64	65
8	1/6/22	66	67	68	69	70	71	72	73	74	75	76	77	78
9	1/7/22	79	80	81	82	83	84	85	86	87	88	89	90	91
10	1/8/22	92	93	94	95	96	97	98	99	100	101	102	103	104
11	1/9/22	105	106	107	108	109	110	111	112	113	114	115	116	117
12	1/10/22	118	119	120	121	122	123	124	125	126	127	128	129	130
13	1/11/22	131	132	133	134	135	136	137	138	139	140	141	142	143
14	1/12/22	144	145	146	147	148	149	150	151	152	153	154	155	156
15	1/13/22	157	158	159	160	161	162	163	164	165	166	167	168	169
16	1/14/22	170	171	172	173	174	175	176	177	178	179	180	181	182
17	1/15/22	183	184	185	186	187	188	189	190	191	192	193	194	195
18
19
20
21
22
23	1/6/22	california	70
Sheet1

Cell Formulas
Range	Formula
B4:N17	B4	=B3+13
C23	C23	=INDEX(B3:N17,MATCH(A23,A3:A17,0),MATCH(B23,B2:N2,0))

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[themev]

Thank you so much!
Yes, the issue was the INDEX was not just the data range and when I fixed that it worked perfectly.

 

[etaf]

you are welcome