Using VLOOKUP Across Multiple Spreadsheets

  • Thread starter Thread starter Brandon
  • Start date Start date
B

Brandon

Can it be done for 5 or more spreadsheets? Just trying to figure out if
there is a way I can get it to look it up across the board so I don't have to
make an Access Database.

Thanks,
Brandon
 
Hi,

Although you can't do 3-D references with VLOOKUP, you can write a formula
of the form

=VLOOKUP(A1,Sheet2!A1:D100,2,FALSE)+VLOOKUP(A1,Sheet3!A1:D100,2,FALSE)+VLOOKUP(A1,Sheet4!A1:D100,2,FALSE) and so on.
 
This does most of what I want it to do, is there anything that can be added
to it to make it show text results as well?
 
Hi Brandon,

Peo Sjoblom sent me a workbook that does a Vlookup across 8 worksheets,(more
if needed). It is brilliant in my estimation. He even e-mailed me an
explanation of how the formula works, which I cannot completely get my head
around.

Here is the formula and must be array entered (Ctrl + Shift + Enter).

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Where: A2 is the look-up value
Where: MySheets is a named range consisting of a list of all the worksheet
you want to look up.
Where: A2:C200 is the lookup array of each sheet.

This will return the lookup value of column C that the lookup value finds on
whatever sheet, note the 3 just before the ,0) at the end of the formula.

You will need to adjust the ranges to suit your workbook.

This is what the formula would look like if you did not use a named range
for the worksheets but instead entered the worksheets names in the formula.
Much better to use a named range. Note that in this formula it returns the
value in Column B per the 2 at the end.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

I will be glad to help you get this set up if you need additional help.

HTH
Regards,
Howard
 
I’m trying to use the formula
=SUMIF('P&Ldata'!$E:$E,$A6,'P&Ldata'!I:I)
It looks at a column on another sheet to determine all the instances of the
word Sales and return the subtotal of figure in a corresponding column.
I have a cell which has in it the text P&Ldata and I want to use this to
change the worksheet the formula looks at, I've looked at lots of suggestions
involving INDIRECT but can't get it to work.
 
Back
Top