Index with Multiple Match Formulas Returning Undesired Value

Paradox07

New Member
Joined
Apr 9, 2010
Messages
8
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
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBT
3Total19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov24-Nov24-Nov24-Nov24-Nov24-Nov24-Nov24-Nov24-Nov24-Nov26-Nov26-Nov28-Nov29-Nov30-Nov3-Dec4-Dec
419:4520:3021:3022:0022:3022:5923:0023:2523:3023:4023:501:002:0016:4517:0018:3019:0019:1519:3019:4520:3021:3022:3022:5923:2523:301:002:0016:4519:1519:3019:4521:3022:5923:2523:3016:4519:1519:3019:4521:3022:5923:2523:3016:4517:0019:1519:3019:4521:3022:5923:2523:3013:0017:3018:0018:1519:0020:2521:1021:3023:3019:1519:4519:4513:0013:0013:001:00
5PP1LTower35200100160010000400013251860801471500111360023020900000000002000101300000000000
6PPFracsDamageLTL8000000000000000000000000000000000000000000000000000000002000000600000
7PPFracsDamageMultiLarge3700000000000000000000000000000000000000000000003070000000100000013130000
8PPFracsDestroy10000000000000000000000000000000000000000000000000000000000000000003610
9PPKindle187000004010040000005336060432110000124140001010230000000017000000000000000000
10PPPremiumsWrap485007001801611015004402214142376509714002403200002002300000000000000100000000000000
11PPRebinLarge181111436757001270312880310000391429072612010236904622497810008451191121493841030154901128948004512407020000578420050200000
12PPRebinMedium67182290490014806228009600797041627513715434742158401057155725930024653168522576118631771153564213302445167027033163273422154039171712682544302400000
13PPSIOCEnv5000000000000040000000000000010000000000000000000000000000000000000000
14PPSingleAuto58643570310010101471750000900021570235870188068242181002515417011080547612651401168251161022235017120100000
15PPSingleAuto58Prem25502200501000800101131271550105600000000000000000000000000000000000000000000
16PPSingleLarge40231140100020301676000080001021962586010707774980014620301014023260122200211106021008300000100000
17PPSingleLargePrem2181512700608001300404312111100307300000000000000000000000000000000000000000000
18PPSingleLargePremFSK8000000000000000000080000000000000000000000000000000000000000000000000
19PPSingleMedPremFSK18300000000100003000001790000000000000000000000000000000000000000000000000
20PPSingleMedium861000000810344000024931001864032761016902621871820032981653204871217376192212258639021462241411621310671315280000000
21PPSingleMediumPrem1145263070039024005700312071471584471120261400000000000000000000000000000000000000000000
22PPSingleNewRelease10000000000000040001020000100010010000000000000000000000000000000000000
23PPTransCGN139480008190000000275400000000000000375000000000000000000000000000000000000000000
24PPTransFRA328820000006000002847000000000000002900000000000000000000000000000000000000000
25PPTransLEJ1602700001778000040920000000000001570000000000000000000000000000000000000000000000
26PPTransORY171200000000000000709000000000000000000000000000000300000000000000000000000
27UnassignedProcessPath34100000000001255016233459480174130397200180000000100050000000000005000000000001
28Total12117433411117181917787486318876409222527792897369973360543696588965826112453231203123713874414365858425376598934011124529054285219640129401327035529597418922765383323501792126745317133611
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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)
 
Upvote 0
Using the exhibit you posted...

A2 houses today's date
A3 houses a time value

Control+shift+enter, not just enter:
Rich (BB code):
=INDEX(C3:BT28,
   MATCH("Total",B3:B28,0),
   MATCH(1,IF(C3:BT3=A2,IF(C4:BT4=A3,1)),0))
 
Upvote 0
@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?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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
Back
Top