Need help with VLOOKUP, I think..

AndersNOR9

New Member
Joined
Nov 13, 2017
Messages
20
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
Joined
Sep 30, 2005
Messages
1,226
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
Joined
Nov 13, 2017
Messages
20
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
Joined
Sep 30, 2005
Messages
1,226
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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! :)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top