Index with Multiple Match Formulas Returning Undesired Value

[Paradox07]

Hello,

I am building a tool for work, and in one of the sections of the main worksheet, I would like the cells to pull specific data from another worksheet that contains the results of a web query. The web query pulls a table of data, with a double header (rows 3 and 4). One column header is for the date and the other is for the time the data below corresponds to. There are also row titles along the left hand side.

I need a formula that will find the column that meets two conditions:

1) Today's date
2) A specific time (referenced from a cell on the main sheet)

It must then pull the data from the cell in that column that is in the row marked "Total".

Here is an example of the data set from the web query:

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT
3		Total	19-Nov	19-Nov	19-Nov	19-Nov	19-Nov	19-Nov	19-Nov	19-Nov	19-Nov	19-Nov	19-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	20-Nov	21-Nov	21-Nov	21-Nov	21-Nov	21-Nov	21-Nov	21-Nov	21-Nov	21-Nov	21-Nov	22-Nov	22-Nov	22-Nov	22-Nov	22-Nov	22-Nov	22-Nov	22-Nov	23-Nov	23-Nov	23-Nov	23-Nov	23-Nov	23-Nov	23-Nov	23-Nov	23-Nov	24-Nov	24-Nov	24-Nov	24-Nov	24-Nov	24-Nov	24-Nov	24-Nov	24-Nov	26-Nov	26-Nov	28-Nov	29-Nov	30-Nov	3-Dec	4-Dec
4			19:45	20:30	21:30	22:00	22:30	22:59	23:00	23:25	23:30	23:40	23:50	1:00	2:00	16:45	17:00	18:30	19:00	19:15	19:30	19:45	20:30	21:30	22:30	22:59	23:25	23:30	1:00	2:00	16:45	19:15	19:30	19:45	21:30	22:59	23:25	23:30	16:45	19:15	19:30	19:45	21:30	22:59	23:25	23:30	16:45	17:00	19:15	19:30	19:45	21:30	22:59	23:25	23:30	13:00	17:30	18:00	18:15	19:00	20:25	21:10	21:30	23:30	19:15	19:45	19:45	13:00	13:00	13:00	1:00
5	PP1LTower	352	0	0	1	0	0	16	0	0	1	0	0	0	0	4	0	0	0	13	25	186	0	8	0	14	7	15	0	0	1	1	1	36	0	0	2	3	0	2	0	9	0	0	0	0	0	0	0	0	0	0	2	0	0	0	1	0	1	3	0	0	0	0	0	0	0	0	0	0	0
6	PPFracsDamageLTL	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	6	0	0	0	0	0
7	PPFracsDamageMultiLarge	37	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	0	7	0	0	0	0	0	0	0	10	0	0	0	0	0	1	3	13	0	0	0	0
8	PPFracsDestroy	10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	6	1	0
9	PPKindle	187	0	0	0	0	0	4	0	1	0	0	4	0	0	0	0	0	0	5	3	36	0	6	0	43	21	10	0	0	0	1	2	4	1	4	0	0	0	1	0	1	0	23	0	0	0	0	0	0	0	0	17	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	PPPremiumsWrap	485	0	0	7	0	0	18	0	16	11	0	15	0	0	44	0	2	2	14	14	237	6	5	0	9	7	14	0	0	2	4	0	32	0	0	0	0	2	0	0	23	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	PPRebinLarge	18111	143	67	57	0	0	127	0	31	288	0	31	0	0	0	0	39	14	290	726	12010	2	369	0	462	249	781	0	0	0	8	45	1191	12	149	38	41	0	30	15	490	11	28	9	48	0	0	4	5	124	0	7	0	20	0	0	0	57	84	2	0	0	5	0	2	0	0	0	0	0
12	PPRebinMedium	67182	29	0	49	0	0	148	0	62	280	0	96	0	0	797	0	4	16	2751	3715	43474	2	1584	0	1057	1557	2593	0	0	246	53	168	5225	76	118	63	177	115	356	42	1330	24	45	16	70	27	0	33	16	327	34	22	15	40	39	17	17	126	82	5	4	4	30	2	4	0	0	0	0	0
13	PPSIOCEnv	5	0	0	0	0	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	PPSingleAuto58	6435	7	0	31	0	0	101	0	147	175	0	0	0	0	9	0	0	0	215	702	3587	0	188	0	68	242	181	0	0	2	5	15	417	0	11	0	8	0	54	7	61	2	6	5	1	4	0	11	6	82	5	11	6	10	2	2	2	35	0	1	7	1	2	0	1	0	0	0	0	0
15	PPSingleAuto58Prem	255	0	2	2	0	0	5	0	10	0	0	8	0	0	1	0	1	1	31	27	155	0	1	0	5	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	PPSingleLarge	4023	114	0	10	0	0	203	0	16	76	0	0	0	0	8	0	0	0	102	196	2586	0	107	0	77	74	98	0	0	1	4	6	203	0	10	1	4	0	23	2	60	1	2	2	2	0	0	2	1	11	0	6	0	2	1	0	0	8	3	0	0	0	0	0	1	0	0	0	0	0
17	PPSingleLargePrem	218	15	12	7	0	0	6	0	8	0	0	13	0	0	4	0	4	3	12	11	110	0	3	0	7	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	PPSingleLargePremFSK	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	PPSingleMedPremFSK	183	0	0	0	0	0	0	0	0	1	0	0	0	0	3	0	0	0	0	0	179	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	PPSingleMedium	8610	0	0	0	0	0	81	0	3	44	0	0	0	0	2493	1	0	0	186	403	2761	0	169	0	262	187	182	0	0	329	8	16	532	0	48	7	12	173	76	19	221	2	25	8	6	39	0	2	1	46	2	24	1	4	11	62	13	106	7	13	15	2	8	0	0	0	0	0	0	0
21	PPSingleMediumPrem	1145	26	30	7	0	0	39	0	24	0	0	57	0	0	312	0	7	14	71	58	447	1	12	0	26	14	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22	PPSingleNewRelease	10	0	0	0	0	0	0	0	0	0	0	0	0	0	4	0	0	0	1	0	2	0	0	0	0	1	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
23	PPTransCGN1	3948	0	0	0	819	0	0	0	0	0	0	0	2754	0	0	0	0	0	0	0	0	0	0	0	0	0	0	375	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
24	PPTransFRA3	2882	0	0	0	0	0	0	6	0	0	0	0	0	2847	0	0	0	0	0	0	0	0	0	0	0	0	0	0	29	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
25	PPTransLEJ1	6027	0	0	0	0	1778	0	0	0	0	4092	0	0	0	0	0	0	0	0	0	0	0	0	157	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
26	PPTransORY1	712	0	0	0	0	0	0	0	0	0	0	0	0	0	0	709	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27	UnassignedProcessPath	341	0	0	0	0	0	0	0	0	0	0	1	25	50	16	23	3	4	5	9	48	0	1	74	1	3	0	39	7	2	0	0	18	0	0	0	0	0	0	0	1	0	0	0	5	0	0	0	0	0	0	0	0	0	0	0	0	5	0	0	0	0	0	0	0	0	0	0	0	1
28	Total	121174	334	111	171	819	1778	748	6	318	876	4092	225	2779	2897	3699	733	60	54	3696	5889	65826	11	2453	231	2031	2371	3874	414	36	585	84	253	7659	89	340	111	245	290	542	85	2196	40	129	40	132	70	3	55	29	597	41	89	22	76	53	83	32	350	179	21	26	7	45	3	17	13	3	6	1	1
Sheet1

I have done quite a bit of looking on the web and tried out several different formulas/variations. I think I'm getting close, but just can't get it quite right. Here are a couple that seem close and their results:

=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0)*AND(MATCH(TODAY(),'Rodeo Data'!$D$3:$JS$3,1)))

This one correctly finds the date and "Total" row, but seems to ignore the MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0) section, which is designed to find the specific time in row 4. It returns either the "Total" row value from the first or last column with the correct date depending on which match type argument I use.

=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(TODAY()&O3,'Rodeo Data'!$D$3:$JS$3&'Rodeo Data'!$D$4:$JS$4,))

This one returns the standard "#N/A" error message.

I am using Windows 7 and Excel 2010. "Rodeo Data" is the name of the sheet that contains the web query data I have included. The J3 and O3 references are cells in the main worksheet that contain the time I want the match function to find.

The desired output from the formula would be if I referenced it to today (20 Nov 2012) and 19:45, it would return the value of 65,826.

It may also be worth noting that the number of columns and rows in the data set is dynamic.

This is my first time posting, so I apologize if I left anything out. Please let me know if you need any further information, and thank you in advance for your help!

 

[gsistek]

Assuming each date-time combination is unique, you could use sumifs.
=SUMIFS(OFFSET('Rodeo Data'!$D$1:$<ACRONYM title=JavaScript>JS</ACRONYM>$1,MATCH("Total",'Rodeo Data'!$B:$B,0),0),'Rodeo Data'!$D$3:$<ACRONYM title=JavaScript>JS</ACRONYM>$3,TODAY(),'Rodeo Data'!$D$4:$<ACRONYM title=JavaScript>JS</ACRONYM>$4,J3)

 

[Aladin Akyurek]

Using the exhibit you posted...

A2 houses today's date
A3 houses a time value

Control+shift+enter, not just enter:

=INDEX(C3:BT28,
   MATCH("Total",B3:B28,0),
   MATCH(1,IF(C3:BT3=A2,IF(C4:BT4=A3,1)),0))

 

[Paradox07]

@gsistek Thanks so much for your help! The formula was initially returning a zero. I stepped through the evaluation steps of the formula, and the MATCH for the "Total" row was correctly grabbing row 28, but then when the OFFSET section of the formula ran it kicked down one row to row 29. I added a -1 after the MATCH formula to bring it back one row and it seems to be working great now! Here's the formula as it looks now:

=SUMIFS(OFFSET('Rodeo Data'!$D$1:$JS$1,MATCH("Total",'Rodeo Data'!$B:$B,0)-1,0),'Rodeo Data'!$D$3:$JS$3,TODAY(),'Rodeo Data'!$D$4:$JS$4,J3)

Does this look good, or did I mess it up and just get lucky?