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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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?
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
If you've got 5 months of actuals in a row, do you want to import months 6-12 to fill out that row?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top