Index/Match formula not working properly from data in Pivot Tables - HELP!

[Pooger]

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!

[avk]

"C3"

Please Login or Register  to view this content.

copy paste down

[Pooger]

Thank you SO much avk!! Can't believe it was so simple (missing the "0" in the match formula)!!! Now if I could only figure out how to do the multiple criteria formula (so I don't have to manually find the correct corresponding Truck column), that would be even better!!

Thanks again for your help ... at the very least I can accomplish what I need to. YAY!

[avk]

if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved & by clicking the Add Reputation button at the foot of one of their posts.".

[Pooger]

I added reputation, but didn't want to mark "solved" just yet, in case someone could help figure out the multiple criteria formula request.

[Bo_Ry]

Please try at B2:Vxx

=IFNA(VLOOKUP($A3,CHOOSE(MATCH(B$2,$B$2:$D$2,),Mileage!$A$2:$I$40,Truck!$A$5:$H$33,Reefer!$A$5:$H$31),MATCH(LOOKUP("Ω",$B$1:B$1),CHOOSE(MATCH(B$2,$B$2:$D$2,),Mileage!$A$1:$H$1,Truck!$A$4:$H$4,Reefer!$A$4:$H$4),),0),"")

[Limor_OP]

Hi
in order to create an even formula that you can drag along the table - you need:

1. to change the titles in row 4 , SUMMARY tab to match the names you gave your tabs.
2. all titles in the tabs Mileage/Truck/Reefer need to start at the same line. I adjusted them to start from line 2

Then in summary tab use this in B5 and across:

Please Login or Register  to view this content.

[Pooger]

Thank you to all who helped!!! Belinda200, I understood your solution and it works great!!! Thank you so much!!!!!