Complex Array Lookup

  • Thread starter Thread starter Monte75
  • Start date Start date
M

Monte75

My data is as such

A B C D E F etc
1 # type J F M A M J J A S O N D
2 112 Budg 9 3 4
3 112 Fore 1 6 9
4 111 Budg 8 1 6
5 111 Fore 3 3 4
6 113 Budg 4 3 2
7 113 Fore 7 2 4


I am using the formula (assuming # is in A1)

{=((A2:A7=111)*(B2:B7="Budg")*(C2:C7))}

This gives me the value 8 found in C4, which is exactly what I am
looking for. The piece I am missing is that I want the (C2:C7) part of
the formula to be dynamic. I would like to have a cell ,located
elsewhere, that stores the month. This one cell would be able to
indicate what I am looking up. By putting March into the cell the
above formula would give me 6 or the value in E4. Suggestions?
 
I assume that this is a part of another formula or else I can't see how you
can get
8 from that formula. Here's one way

=((A2:A7=111)*(B2:B7="Budg")*(OFFSET(B2,,VLOOKUP(O1,{"January",1;"February",
2;"March",3;"April",4;"May",5;"June",6;"July",7;"August",8;"September",9;"Oc
tober",10;"November",11;"December",12},2,0),6,)))

Where O1 is the cell where you would put the name of the month, it could be
simplified to

=((A2:A7=111)*(B2:B7="Budg")*(OFFSET(B2,,VLOOKUP(O1,MyTable,2,0),6,)))

where MyTable would be a 2 column table with the months to the left and
their number to the right,
if you could just put the number of the month (3 for March) in O1 it could
look like

=((A2:A7=111)*(B2:B7="Budg")*(OFFSET(B2,,O1,6,)))

the 6 at the end is the equivalent of the number of rows 2,3,4,5,6,7 and it
can be dynamic as well

=((A2:A7=111)*(B2:B7="Budg")*(OFFSET(B2,,O1,COUNT(B:B),)))
 
Peo,

You were right about my original formula not returning the number you
though it would. I left out
{=*sum*((A2:A7=111)*(B2:B7="Budg")*(OFFSET(B2,,O1,6,))}

Anyway, your solution worked like a charm when I added the sum that I
forgot in. I truly appreciate your help. Thank you very much!
 
Back
Top