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,
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,139
Messages
5,576,308
Members
412,716
Latest member
thviid
Top