Array Help Please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If I have a spreadsheet with the following table:

Item Number Jul-03 Aug-03 Sep-03 Oct-03
ABC 15 20 50 15
CDE 20 16 - 5
FGH 1 8 22 -
IJK 6 40 - -
LMN 16 22 30 8

How can I create an arry that will give me the following output:

Item Date Qty
ABC Jul-3 15
ABC Aug-3 20
CDE Jul-3 20
CDE Aug-3 16

I am assuming I need an array formula. Thank you in advance for any help!

Jennifer
 
Jennifer,

As explained in your last post you don't need an array formula. With your
data laid out as you describe then the formula:-

A10= abc
B10 = jul-03
=SUMPRODUCT(($A$2:$A$6=A10)*($B$1:$E$1=B10)*($B$2:$E$6))

looks for abc (not case sensitive) in column A. Jul-03 in row 1 and returns
the value at the intersect(s) of these 2 if it finds them.

Col A Col B Col C Col D Col E
Item Number Jul-03 Aug-03 Sep-03 Oct-03>
ABC 15 20 50 15
CDE 20 16 - 5
FGH 1 8 22 -
IJK 6 40 - -
LMN 16 22 30 8

Mike
 
Yes, Mike, I'm sorry but I completely misunderstood your response on my last
post. I tried your suggestion and it worked. Thank you so very much for
your help (twice!) :)
 
Back
Top