I am trying to get a Vlookup to search for value across multiple workbooks
Can I combine all these in one formula ?
VLOOKUP (A2, '[Workbook1.xls]Sheet1'!G2:H5,2,false)
(A2, '[Workbook2.xls]Sheet1'!G2:H5,2,false)
(A2, '[Workbook3.xls]Sheet1'!G2:H5,2,false)
(A2, '[Workbook4.xls]Sheet1'!G2:H5,2,false)
(A2, '[Workbook5.xls]Sheet1'!G2:H5,2,false)
(A2, '[Workbook6.xls]Sheet1'!G2:H5,2,false)
(A2, '[Workbook7.xls]Sheet1'!G2:H5,2,false)
(A2, '[Workbook8.xls]Sheet1'!G2:H5,2,false)
Thanks
Are you using Excel 2003?
What type of data is the formula returning? Is it text? Numbers? Could be either? Something else?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
its Excel 2010 ..will be returning both text and numbers..nothing else
Thanks
You could do something like this...
=IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(A2,[Workbook1.xls]Sheet1!$G$2:$H$5,2,0),VLOOKUP(A2,[Workbook2.xls]Sheet1!$G$2:$H$5,2,0)),VLOOKUP(A2,[Workbook3.xls]Sheet1!$G$2:$H$5,2,0)),VLOOKUP(A2,[Workbook4.xls]Sheet1!$G$2:$H$5,2,0)),"")
In that formula I only reference 4 files. If you're actually wanting to reference 8 files then you can see that the formula will be quite long.
You may be better off using 8 separate formulas and then pulling the result from one of those 8 cells.
it's long but it works
=IFERROR(VLOOKUP(A2, [Workbook1.xls]Sheet1!G2:H5,2,FALSE),IFERROR(VLOOKUP(A2, [Workbook2.xls]Sheet1!G2:H5,2,FALSE),IFERROR(VLOOKUP(A2, [Workbook1.xls]Sheet3!G2:H5,2,FALSE),IFERROR(VLOOKUP(A2, [Workbook1.xls]Sheet4!G2:H5,2,FALSE),IFERROR(VLOOKUP(A2, [Workbook1.xls]Sheet5!G2:H5,2,FALSE),IFERROR(VLOOKUP(A2, [Workbook1.xls]Sheet6!G2:H5,2,FALSE),IFERROR(VLOOKUP(A2, [Workbook1.xls]Sheet17!G2:H5,2,FALSE),IFERROR(VLOOKUP(A2, [Workbook1.xls]Sheet8!G2:H5,2,FALSE),""))))))))
Good deal. Thanks for the feedback!
apologies ..only had the chance to test it now...it wasn`t suscessfull unfortunately..error message saying maximum number of nested formula have been exceeded
Excel versions 2003 and earlier only allow 7 levels of nested functions.
Excel versions 2007 and later allow 64 levels of nested functions.
you wright ,,it accept more nesting when saved under the latest formats..
one small issue, does it matter how many sheet on each work book ..am I wright to say that the name of the sheet that s all matter ? seems having trouble locating the array...Thanks
I don't know if there's a limit as to how many external files you can link to. However, if your formula is trying to link to very many I think you're better off using a separate formula for each file as I noted in my earlier reply.
there are 8 workbook to link...
Column 2 actually have value that would Determine in which workbook the array is..
Just wonder if the If function can shorten the formula
IF C2=”Adam Then =VLOOKUP(A2, [Adam.xls]Sheet1!G2:H5,2,FALSE)
IF C2 =Ahmed Then = VLOOKUP(A2, Ahmed.xls]Sheet1!G2:H5,2,FALSE),
IF C2 =”Anja” Then = VLOOKUP(A2, [Anja.xls]Sheet1!G2:H5,2,FALSE),
IF C2 =Sue Then = VLOOKUP(A2, [Sue.xls]Sheet1!G2:H5,2,FALSE),
IF C2 =John Then = VLOOKUP(A2,[john]Sheet1!G2:H5,2,FALSE),
IF C2 =Steve Then = VLOOKUP(A2, [steve.xls]Sheet1!G2:H5,2,FALSE),
IF C2 =Aicha Then = VLOOKUP(A2, [Aicha.xls]Sheet1!G2:H5,2,FALSE),
IF C2 =karen Then = VLOOKUP(A2, [Workbook2.xls]Sheet1!G2:H5,2,FALSE),
Hum maybe I m just going around here ..
Still going to be a long formula.
List the names in a range of cells...
A10 = Adam
A11 = Ahmed
A12 = Anja
A13 = Sue
A14 =John
A15 =Steve
A16 =Aicha
A17 =karen
Then...
=IF(C2="","",VLOOKUP(A2,CHOOSE(MATCH(C2,A10:A17,),[Adam.xls]Sheet1!G2:H5,[Ahmed.xls]Sheet1!G2:H5,[Anja.xls]Sheet1!G2:H5,[Sue.xls]Sheet1!G2:H5,[john.xls]Sheet1!G2:H5,[steve.xls]Sheet1!G2:H5,[Aicha.xls]Sheet1!G2:H5,[Workbook2.xls]Sheet1!G2:H5),2,0))
yes you right still long and prone to many potential error..looks to me I ll have to try and search for VBA code
Thanks for your help
You're welcome.
Good luck!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks