Need help with VLOOKUP, I think..

[AndersNOR9]

Hi, I have an Excel workbook with a lot of sheets. The first sheet is an overview of revenues and other metrics for different firms for the years 2010 - 2016. All the other sheets are the financial statements for these firms. I have all the names for the firms in the A column and 2010 - 2016 in B, C, D, E, F, G, H, I. The names in column A corresponds to the names of each sheet. I want excel to get me the values I need for all the firms for all the years without having to do it manually. Any tips?

https://imgur.com/a/0iwatUJ

 

[JazzSP8]

You could use INDIRECT with the names in Column A to determine the sheet name.

Something like...

=VLOOKUP(VALUE,INDIRECT("'"&A17&"'!"&"Range"),COL,FALSE)

 

[AndersNOR9]

Hi,

Thanks for the quick reply! What should I do with the range part? Do I have to manually go through every 75 sheets and define the array? array1, array2 etc.

 

[JazzSP8]

What I would do with the range is just define the table once, then use a helper cell to change the COL number.

So, looking at your worksheet you have some nice blank cells in Row 15 - I'd put numbers in those to replicate the Columns. Using the VLOOKUP to look for 2010 for example;

In Cell B15 enter 8, in Cell B16 put '=B15-1' and drag along as you need to do, this should give you a decreasing value for your COL number (If you need to hide these for aesthetic value then just change the Text colour to the background colour).

Your formula would then be something along the lines of;

=VLOOKUP($A17,INDIRECT("'"&$A17&"'!"&"$A:$K"),B$15,0)

When you drag the formula it should automatically update based on the Lookup value in Column A and the COL number in Row 15

Hope all that makes sense!