multi-level indexing?

[44judester]

Variables from which to choose: Month, Store#, Type (Fruit or Veg), Color

Table looks like this:
Fruit
Vegetable
Month Store# Red Green Orange Red Green Orange




The data is the inventory of red fruits, green vegetables, etc in each store
at the end of each month.

What does my formula look like to lookup that inventory volume for a given
set of variables?

 

[Luke M]

You can use SUMPRODUCT for multi-conditions. For red fruit from store1 in
january something like
=SUMPRODUCT(--(A2:A100="January"),--(B2:B100="Store1"),--(C2:C100="Fruit"),(D210))

This assumes month is in column A, store in column B, Type in column C, and
inventory is in column D. Try playing around with the different critieria to
get what you need. Note that the arrays need to be same size, and unless
using XL 2007, you can't callout entire column (A:A).

 

[44judester]

Thanks for the reply, Luke. SUMPRODUCT treats non-numeric entries as zero so
I keep getting a zero result.

The way lined up in my example was a bit misleading....this is my real-life
example:

Aug Sep
------ ------
Dept Shift ST OT ST OT
------ ------- ------ ------ ------ -------
443 1st 493 41 986 81
443 2nd 493 41 986 81
443 3rd - - - -
444 1st 141 16 282 32
444 2nd 141 16 282 32
444 3rd - - - -
445 1st 422 48 845 102
445 2nd 352 40 704 85
445 3rd - - - -
448 1st 211 15 422 30
448 2nd 282 20 563 40
448 3rd 70 5 141 10
449 1st 563 27 1,126 55
449 2nd 422 21 845 41
449 3rd 70 3 141 7

this is an exerpt from a large table of straight-time and overtime by month,
dept and shift. I want to be able to pull data from this (just one
intersection point) into other spreadsheets given the month, dept, shift and
whether ST or OT. What is that formula in the other spreadsheets?

 

[NBVC]

Say your table is in Sheet1!A1:F17, where:

A3:A17 list your Depts
B3:B17 list your Shifts
C1 shows Oct and E1 shows Sep
C2:F2 lists ST or OT, respectively for the months


then in Sheet2:

A2: Dept (e.g 444)
B2: Shiif (e.g. 2nd)
C2: Month (e.g Sep)
D2: ST or OT (e.g. OT)

then formula to extract intersect:


Code
-------------------
=INDEX(Sheet1!$C$3:$F$17,MATCH(A2&B2,Sheet1!$A$3:$A$17&Sheet1!$B$3:$B$17,0),MATCH(1,(Sheet1!$C$2:$F$2=D2)*((Sheet1!C1:F1=C2)+(Sheet1!B1:E1=C2)),0)
-------------------


this formula is an array formula and must be confirmed wit
CTRL+SHIFT+ENTER not just ENTER

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[44judester]

Not sure how that last MATCH sequence works, but it does! Thanks!

 

[NBVC]

Not sure how that last MATCH sequence works, but it does! Thanks!


Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------

The last match looks at two conditions... first whether the 2nd row i
OT or ST... and the other conditon checks for the Month... since yo
only have months on every other cell in the row.. it needs to check i
the cell in the same column as the OT, ST is filled or not.. the + sig
acts as an OR checking if cell in same column or cell in column to th
left has the correct month.

The 1 at the beginning, looks for the first position of a 1 in th
resulting array of this lookup array (which is also a conditiona
array): (Sheet1!$C$2:$F$2=D2)*((Sheet1!C1:F1=C2)+(Sheet1!B1:E1=C2)

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com