1

Is it possible to insert a VLOOKUP inside a VLOOKUP formula, to search for a specific table_array?

This is the formula I want to use:

=VLOOKUP($F492,CONCATENATE("'[Budget estimate Mar 2016 v2.xlsm]",VLOOKUP($I492,Sheet2!$C$3:$D$73,2,0),"'!$B$23:$T$116"),8,0)

The reason I used CONCATENATE is so table_array does not change when I drag the formula down, but worksheet name changes.

The problem is VLOOKUP returns me a value error. Should I insert some another function to change it to text?

What I want to have inside the VLOOKUP is table_array. Normally you have:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

My table_array changes depending on specific value in column "I". The way it changes, it just changes the name of the worksheet, so if I have a table array:

'[Name1'!B23:T116 for value "example1",

I want it to change to:

'[Name2'!B23:T116 for value "example2".

That's why I used CONCATENATE, so the VLOOKUP will look for a specific Name"x" and CONCATENATE will join B23:T116.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Lukas
  • 392
  • 1
  • 4
  • 13

2 Answers2

3

Not sure it does what you require, but the syntax is OK: 

=VLOOKUP($F492,INDIRECT("'[Budget estimate Mar 2016 v2.xlsm]"&VLOOKUP($I492,Sheet2!$C$3:$D$73,2,0)&"'!$B$23:$T$116"),8,0)
pnuts
  • 58,317
  • 11
  • 87
  • 139
1

I think you'll probably need to put that CONCATENATE function and its contents inside an INDIRECT function.

Guy Hodges
  • 157
  • 2
  • 11
  • ok what I want to have inside the vlookup is table array. Normally you have like that: '=vlookup=(lookup_value, table_array, col_index_num, [range_lookup]). My table_array changes depending on specific value in column "I". The way it changes, it just changes the name of the worksheet, so if I have a table array '[Name1'!B23:T116 for value "example1", I want it to change to '[Name2'!B23:T116 for value "example2). Thats why i used concatenate, so the vlookup will look for a specific Name"x" and concatenate will join B23:T116. – Lukas Mar 25 '15 at 17:55
  • 1
    I see that you want to construct a reference with the concatenates, but you'll need that to be put inside an INDIRECT function for it to be considered a reference rather than a bunch of text. It's possible to do this BUT the workbooks you are referencing are going to need to be open. To use dynamic references to closed books, you'll need the famous PULL function – Guy Hodges Mar 25 '15 at 18:09
  • But I have 300 workbooks, I don't want to put their names manually. Anyway it's not working, got REF! error. =VLOOKUP($F492,INDIRECT(CONCATENATE("'[Budget estimate Mar 2016 v2.xlsm]",VLOOKUP($I492,Sheet2!$C$3:$D$73,2,0),"'!$B$23:$T$116"),0),8,0) – Lukas Mar 25 '15 at 18:12
  • yeah, I can understand for that number of workbooks, you don't want to be opening and closing them all. INDIRECT (like @pnuts has used in his answer) only works on opened books. To do what you want, you'll need to get a user-defined function from this site: http://numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/ – Guy Hodges Mar 26 '15 at 13:49