Horrible Lookup

kathleen422

New Member
Joined
Jan 16, 2004
Messages
40
I have Budget Data (12 months worth) on a sheet named Lookup and I have actual data by month on the sheet named Actual

Actual Data can be from 1 - 12 months, data is laid out

Acct Mth1 Mth2 Mth3 ..... ----> mth12

I can find out where the Actual Data ends by doing a Selection.end(xlToRight) and then calculating what Column I'm in on the Actual Sheet. I then need to go to the Lookup Sheet and return the amount in that column tto get the budgeted amount.

Dim CurrentMonth as Integer
Range("A1").select
Selection.end(xltoRight).select
CurrentMonth= Activecell.column

By thought pattern was I should then be able to apply the following formula:

LOOKUP is the sheet where the data resides
AP2 is where the Acct Number resides that I am looking up on

=IF(ISERROR(VLOOKUP(AP2,LOOKUP!$A$1:$N$10000,CurrentMonth,0)),0,VLOOKUP(AP2,LOOKUP!$A$1:$N$10000),CurrentMonth,0))

By now I guess you have figured out that it is not working with the declared variable. The ending column can vary monthly so I can't hardcode it

This formula is being used to go find the Budget Amount for that Month in the Lookup sheet

Appreciate any help you might be able to provide.

Thanks,
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Is it that you want to pull budget numbers into the actuals sheet to fill out the current year where you have no actuals as yet?
 

kathleen422

New Member
Joined
Jan 16, 2004
Messages
40
Not quite yet, that is next step

I know what my actual is up to that point in time, now I want to find out what my budget is.

Next step pull remaining budget
 

kathleen422

New Member
Joined
Jan 16, 2004
Messages
40
Figured it out: FINALLY have spent hours on this:

Heres the fix

=IF(ISERROR(VLOOKUP(AP2,LOOKUP!$A$1:$N$10000," & CurrentMonth & ",0)),0,VLOOKUP(AP2,LOOKUP!$A$1:$N$10000)," & CurrentMonth & ",0))
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
If you've got 5 months of actuals in a row, do you want to import months 6-12 to fill out that row?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,625
Messages
5,832,750
Members
430,163
Latest member
YesImAk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top