Changing Table Array Size in VLOOKUP Automatically

I'm downloading a spreadsheet with several years of financial information. Each financial statement is broken out into its own sheet (Income statement, Balance sheet, etc). Each sheet is broken out by year (each column), with financial numbers below.

I created a new sheet, and am working on a VLOOKUP format to pull out the specific information I need from each sheet. Example:

=VLOOKUP($A3,'Balance Sheet'!$A$13:$J$95,2,)

This VLOOKUP matches the line-item wording on my column (such as "Cash"), then returns the corresponding number for a given year in another sheet.

A few issues:

1) Is there a way to automatically change the table array size in my vlookup? The number of columns in each sheet can be variable (anywhere from 3-10 years), and the number of row items could be very wide (from 50-150). How do I make sure that I capture the correct array in an automated fashion?

1a) Is there a way to auto-detect how many columns of data are included in each sheet? I'd like to build my template sheet for a set number of years, but sometimes I get fewer years of info..If possible, I need it to line up to the right automatically (not the left). So if my sheet is, with spots for data below:

1 2 3 4 5

And I get 3 years of historical, I need it to show up as:
1 2 3 4 5
- - X X X

Not
1 2 3 4 5
X X X - -

2) Is there a way for VLOOKUP to ignore spaces when matching in another sheet?

3) Is there a way (macro?) to auto-create a sheet with all of this info/formulas plugged in?

Thanks!

Okay, so I switched to the entire column format, which seems to still be working correctly:

VLOOKUP($A2,'Balance Sheet'!$A:$K,2,)

This should account for making sure I get the required number of rows...

Not 100% sure on my other question, another try at explaining it. Let's say my template sheet is setup like this:

Year 1 Year 2 Year 3

And in another sheet, I find out i only have 2 years of financials..

#1 #2

I need it to look-up and return:

Yr1 Yr2 Yr3
XX #1 #2

Not:

Yr1 Yr2 Yr3
#1 #2 XX

Right now, the VLOOKUP is using the second method.

Do I need to switch everything to the Index/Match functions in order to accomplish this? And what about if the number of years is dynamic?

To summarize, I'll have a 10 column square on my template sheet, and always need to fill it so the numbers are lined up with the rightmost column, leaving the initial columns blank (or if there are 10 years of financials in the second sheet, the entire square will be filled as normal)

Thank you!

sample.xlsx

File should be attached - there is data in Sheet1 and Sheet2

Sheet 1 is the template.
Sheet 2 holds the data.

Sheet 2 could hold anywhere from 3 to 10 columns of information, depending on how many years of information is available (in this case 3). The template will always have 10 columns of information, but I need the data to be displayed as shown (left to right, but ending in the 10th column).

So if there are 5 years of info, it ideally needs to recognize that there are 5 columns, and then place them into the template sheet in G:K (or #6-10).

Wow, getting much closer! Why does column H return zeros, and can those be eliminated?

Also, my full formula right now is:
=IF(ISNA(VLOOKUP($A2,'Balance Sheet'!$A:$K,2,)),0,IF(VLOOKUP($A2,'Balance Sheet'!$A:$K,2,)="-",0,VLOOKUP($A2,'Balance Sheet'!$A:$K,2,)))

In order to count for #NAs and - which are sometimes in the data set - I need these to be replaced with 0s when pulled over into the template. Are these modifications possible?

Originally Posted by DGagnon

where does the Vlookup come into play on this version? also here is the update to remove the 0's from my formula. this entry is for K2 of the sample sheet.

=IF(COUNTA(Sheet2!$B2:$K2)-COUNTA(K$1:$K$1)+1=0,"",IFERROR(INDEX(Sheet2!$B2:$K2,COUNTA(Sheet2!$B2:$K2)-COUNTA(K$1:$K$1)+1),""))

I was just showing my original VLOOKUP code to show how I accounted for N/As and -'s which randomly show up in the sheet (and I need to get rid of them in the template.)

This formula seems to break down if the Cash & Current Liabilities are in a different row - i.e. if I move them down to A4 and A5 on Sheet2 - then I just get blank cells..The order of the account names to match in column A could be random..

If the numbers mentioned in Sheet1 1 to 10 you are referring to Years, try the below one. You Sample file also attached FYR.

=SUMPRODUCT((B$1=Sheet2!$B$1:$D$1)*(Sheet1!$A2=Sheet2!$A$2:$A$3)*(Sheet2!$B$2:$D$3))

Originally Posted by hhbinuae

If the numbers mentioned in Sheet1 1 to 10 you are referring to Years, try the below one. You Sample file also attached FYR.

=SUMPRODUCT((B$1=Sheet2!$B$1:$D$1)*(Sheet1!$A2=Sheet2!$A$2:$A$3)*(Sheet2!$B$2:$D$3))

Hmm, seems like it could be an elegant solution, but what happens if the year could start in any row on Sheet2? And could this be expanded to cover a general number of columns, say B to K?

Originally Posted by DrSues02

Hmm, seems like it could be an elegant solution, but what happens if the year could start in any row on Sheet2? And could this be expanded to cover a general number of columns, say B to K?

The formula works this way:

First array checks for the year (which can be expanded till XFD also )
Second checks for cash, current liabilities etc (Please note the naming convensions should be exact as in your data)
Third captures the data basis the first and second checks.

This will calculate and put 0 if there is no data available. Also if you need "-" instead of 0, you can us
IFERROR(Excel 2007 onwards) or IF(ISERROR

Thanks for working with me on this!

I've attached the actual spreadsheet which will hopefully clear up any confusion.
EF Sample.xls

With my current setup, the problem is that I only received 9 years of info on the Income Statement & Balance Sheet, but I have 10 years on my template, so it leaves the last column blank & gives errors..

1) I basically need to "shift" all of the data on the template to the right (so its up againt the dotted line), leaving earlier columns blank instead
2) I need it to auto-calculate how many columns of data are available on the IS & BS, so if I only have 3 years of income statement and balance sheet data, then B:H will be blank on the Template
3) From above, it needs to handle multiple lookup areas on the IS/BS (the data will not always start on A2, it could start on A28).

Any luck with the updated spreadsheet? Should show everything clearly now..

Just trying to automate the process, as I would have to change that VLOOKUP each time I open a sheet across hundreds of sheets..