I am trying to create a SUMMARY page (tab) that draws data from 3 separate Pivot Tables (generated from downloaded reports).
Image 2.gif
However, my Index/Match formulas (i.e.=IFERROR(INDEX(Mileage!$B$2:$B$40,MATCH($A3,Mileage!$A$2:$A$40)),0) in each of the "Mileage", "Gallons" & "Reefer" columns isn't working properly -- meaning it is populating inaccurate data into the STATE rows (notice rows 8 & 9 in picture above ---- it is populating the data for CO into CT & DE when CT & DE should be 0).
Since the downloaded reports do not include STATES that have no data, ALL the individual states do not appear on my pivot tables, but I need all the individual states on my SUMMARY, even if the data is zero. I'm not sure if this is creating part of the problem or not.
I've attached my Worksheet.
Ideally, since the Truck #'s don't appear in the same sequence on each of my Pivot Tables to the sequence in my SUMMARY, I'd like to create a formula that will also find the TRUCK # and match the corresponding "Mileage", "Gallons" & "Reefer" data to the STATE. Find Truck (row 1) and match the data found in the "Mileage", "Gallons" & "Reefer" pivot tables to the corresponding "Mileage", "Gallons" & "Reefer" columns for that truck in the SUMMARY for each of the STATES by Truck. Basically, a multiple criteria index/match formula (which I struggle with). If that makes sense.
I can't figure out why the basic index/match formula isn't working, nor can I figure out a formula to accomplish the multiple index/match criteria described above.
Any help and advice would be greatly appreciated! Thank You!
Bookmarks