# Need help with VLOOKUP, I think..

#### AndersNOR9

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

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### JazzSP8

##### Well-known Member
You could use INDIRECT with the names in Column A to determine the sheet name.

Something like...

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

Last edited:

#### AndersNOR9

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

##### Well-known Member
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;

Code:
``=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!

Replies
4
Views
185
Replies
7
Views
513
Replies
8
Views
263
Replies
5
Views
499
Replies
3
Views
421

1,170,966
Messages
5,873,030
Members
432,956
Latest member
j269

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