vlookup(concatenate())

  • Thread starter Thread starter bostitch
  • Start date Start date
B

bostitch

I need data from a workbook with the prefix in Col B,
with ".xls" concatenated as the suffix. Much obliged for
any advice.

A B C
1 4407007 =VLOOKUP(A1,CONCATENATE(B1,".xls")!
$B$1:$D$750,2,FALSE)
1 4407008 =VLOOKUP(A1,CONCATENATE(B1,".xls")!
$B$1:$D$750,2,FALSE)
 
I didn't test this, but I'm guessing this is what you want
to put in column C (I'm also assuming the sheet name in
the workbook you're referencing is called "Sheet1" - you
might have to change that):

=VLOOKUP(A1,indirect("'["&B1&".xls]Sheet1'!
$B$1:$D$750"),2,FALSE)
 
Note that, as opposed to vlookup, indirect needs the workbooks to be open,
just in case the OP had a path to a closed wb in a cell..

--

Regards,

Peo Sjoblom


Karen said:
I didn't test this, but I'm guessing this is what you want
to put in column C (I'm also assuming the sheet name in
the workbook you're referencing is called "Sheet1" - you
might have to change that):

=VLOOKUP(A1,indirect("'["&B1&".xls]Sheet1'!
$B$1:$D$750"),2,FALSE)

-----Original Message-----
I need data from a workbook with the prefix in Col B,
with ".xls" concatenated as the suffix. Much obliged for
any advice.

A B C
1 4407007 =VLOOKUP(A1,CONCATENATE(B1,".xls")!
$B$1:$D$750,2,FALSE)
1 4407008 =VLOOKUP(A1,CONCATENATE(B1,".xls")!
$B$1:$D$750,2,FALSE)
.
Click to expand...
Click to expand...
 
Back
Top