Help with lOOKUP?

[Guest]

I have a table that looks like the following example.

Date LOC ACCT Beg Bal Activity Ending
May-06 M101 1001 10.00 210.00 310.00
May-06 M101 1002 20.00 220.00 320.00
May-06 M101 1003 30.00 230.00 330.00
May-06 M101 1004 40.00 240.00 340.00
May-06 M101 1005 50.00 250.00 350.00
Jun-06 M101 1001 60.00 260.00 360.00
Jun-06 M101 1002 70.00 270.00 370.00
Jun-06 M101 1003 80.00 280.00 380.00
Jun-06 M101 1004 90.00 290.00 390.00
Jun-06 M101 1005 100.00 300.00 400.00
May-06 M102 1001 110.00 310.00 410.00
May-06 M102 1002 120.00 320.00 420.00
May-06 M102 1003 130.00 330.00 430.00
May-06 M102 1004 140.00 340.00 440.00
May-06 M102 1005 150.00 350.00 450.00
Jun-06 M102 1001 160.00 360.00 460.00
Jun-06 M102 1002 170.00 370.00 470.00
Jun-06 M102 1003 180.00 380.00 480.00
Jun-06 M102 1004 190.00 390.00 490.00
Jun-06 M102 1005 200.00 400.00 500.00

I have a spreadsheet that I need to fill out every month in a specific
format so I need to have something that will pull info from this table so I
don’t have to type it in by hand every month. For example I may need to
have the specific amount in the Activity column for a certain month location
& account. So I need to have a lookup search through this table using the
Month, LOC, & Acct as criteria so it can return the right amount in the
activity column. SO I will have control cells with DATE, LOC, ACCT. Please
help this is daunting task.

Thanks
Mascot

 

[Guest]

there was a typo in my formula - guess you didn't spot it. Also, this makes
the month match more robust as long as column A contains true date values:

=SUMPRODUCT(--(MONTH(Sheet2!$A$2:$A$20)=MONTH(B7)),--(Sheet2!$B$2:$B$20=B8),--(Sheet2!$C$2:$C$20=B9),Sheet2!$E$2:$E$20)

 

[Guest]

Hi Tom,

Maybe I am puting the formula in the wrong place. I have the data in sheet2
like you said. On the next sheet (sheet3) I put May06 (5/1/06) in B6, M101 in
B7, 1001 in B8. I then put the formula in C7, but I am not getting the
anything except VALUE#. What am I doing wrong?

Mascot

 

[Guest]

As written, the formula is looking for those values in B7, B8, B9 and you are
putting them in B6, b7, b8

When i use your numbers in the right cells the formula returns 210. (for
activitiy)

 

[Guest]

Hi Tom,

I must be missing something. if you could can you email me a copy of the
spreadsheet (e-mail address removed). Also I am using excel 2000. I don't know if
this makes a difference.

Thanks
Mascot

 

[Guest]

sent.

--
Regards,
Tom Ogilvy

Hi Tom,

I must be missing something. if you could can you email me a copy of the
spreadsheet (e-mail address removed). Also I am using excel 2000. I don't know if
this makes a difference.

Thanks
Mascot

 

[Guest]

Also - another thought - , if you were doing something like

=sumproduct(--(sheet2!$A:$A=B7),--(sheet2!$B:$B=B8), etc

that won't work. You can not use entire column references in this type of
formula. Plus, the more cells you check, the slower the formulas are to
recalculate, so you would generally want to be conservative in defining your
range and the number of rows in each part must be equal.