Help With LOOKUP Formula That Utilizes Multiple Criteria

Dmerric1, Good morning.

Try to use:

E6 -->
=INDEX(Data!$C$3:$G$11,MATCH(E$5,Data!$A$3:$A$11,0)+ROW(A6)-6*1,MATCH($C$2,Month,0))

Copy it down and across until G8

Take a look at it:
http://speedy.sh/cxc2T/26-08-2014-In...y-Month-OK.xls

Is it what you want?
I hope it helps.

The ROW(A6) returns the row number, which is 6, and subtracts 6 from it to make it 0
The *1 I don't know why that is in there o.O. Normally you would do something like this to force a value to be a number, but that function returns only numbers so idk.

So the ROW function serves as an offset to get the value +0, +1, and +2 rows down from the result, giving Labor Parts and Loads respectively. It works the same as ROWS($1:1)-1


The problem with this solution method is it will only work if the three criteria are in the same order all the way down. If Loads and Labor are backwards (Loads, Parts, Labor in that order on Data tab), it will pull the data backwards as well. If it's all in the same order, this works fine though!

Try this..
Simply drag and drop horizontally and vertically..

Formula:

Please Login or Register  to view this content.

Don't forget to click *