# Horrible Lookup

#### kathleen422

##### New Member
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

Thanks,

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?

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

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))

If you've got 5 months of actuals in a row, do you want to import months 6-12 to fill out that row?

Replies
5
Views
355
Replies
4
Views
302
Replies
0
Views
323
Replies
12
Views
490
Replies
9
Views
477

1,214,437
Messages
6,119,519
Members
448,903
Latest member
StephMJ

### 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.

### Which adblocker are you using?

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

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