Using VLOOKUP but would prefer INDEX MATCH MATCH w/SUMIFS or SUMPRODUCT

Hi,

I'm trying to cull data from a spreadsheet that has one column against three row headers. Thought an advanced INDEX MATCH MATCH formula would be perfect, maybe with SUMPRODUCT or SUMIFS combined; however, I'm stuck. Have to use lame VLOOKUP formula. Ugh!

In the STAT tab, column A is cities. First row is a variable (e.g., counts, hours, max hours, revenues). Second row is type of truck and weight class. Third row is month.

Sheet2 tab has the formulas to reconfigure the data. Yellow highlights show my attempts to avoid VLOOKUP but can't be copied throughout the sheet. Beginning in Row 11, VLOOKUP is used exclusively.

Thanks in advance for your help!

you could use something like:

Formula:

Please Login or Register  to view this content.

note: your REVENUE values for Medium/Heavy Duty (rows 165 down) should read TRUCK REVENUE

Brilliant. Thank you both!