VLookup a Value Based on a Single Criteria Across Multiple Workbooks

[jakecutler]

Hi All,

I am trying to put a formula together that is essentially a VLOOKUP that looks through 3 separate workbooks (separate excel files, not tabs/sheets within 1 workbook).

It is to look up a defined number that could be in either of the 3 workbooks and then show the allocated name to that number.

Im sure its relatively easy to those experts out there, but with my limited self taught skills i cant quite get it.

I found this example i was using as a basis:

=IF(ISNA(VLOOKUP(B9,Sheet1!D3:I3,2,0)),IF(ISNA(VLOOKUP(B9,Sheet2! D3:I3,2,0)),VLOOKUP(B9,Sheet3!D3:I3,2,0),VLOOKUP(B9,Sheet2! D3:I3,2,0)),VLOOKUP(B9,Sheet1! D3:I3,2,0))

which came from here http://xl-central.com/lookup-single-...le-sheets.html

This is what i have come up with but it doesnt work:

=IF(ISNA(VLOOKUP(B9,'\\OEPAPVFLS01\Shared\631-Assessment and Compliance Services\Shared Data\Assessments\Branch Project Trackers\[M&I North - Branch Workload Tracker.xlsm]NORTH_CASELOAD'D3:I3,2,0)),IF(ISNA(VLOOKUP(B9,'\\OEPAPVFLS01\Shared\631-Assessment and Compliance Services\Shared Data\Assessments\Branch Project Trackers\[M&I South - Branch Workload Tracker.xlsm]SOUTH_CASELOAD'D3:I3,2,0)),VLOOKUP(B9,'\\OEPAPVFLS01\Shared\631-Assessment and Compliance Services\Shared Data\Assessments\Branch Project Trackers\[Infrastructure - Branch Workload Tracker.xlsm]INFRASTRUCTURE_CASELOAD'!D3:I3,2,0),VLOOKUP(B9,'\\OEPAPVFLS01\Shared\631-Assessment and Compliance Services\Shared Data\Assessments\Branch Project Trackers\[M&I South - Branch Workload Tracker.xlsm]SOUTH_CASELOAD'D3:I3,2,0)),VLOOKUP(B9,'\\OEPAPVFLS01\Shared\631-Assessment and Compliance Services\Shared Data\Assessments\Branch Project Trackers\[M&I North - Branch Workload Tracker.xlsm]NORTH_CASELOAD'D3:I3,2,0))

Any assistance would be much appreciated.

Jake

[Evolta]

I'm like you a self taught person with limited skills, but I stripped the formula and noticed:

CASELOAD'D3:I3
CASELOAD'D3:I3
CASELOAD'!D3:I3
CASELOAD'D3:I3

Only one has a "!" in it. Maybe fixing that will help.

[Evolta]

Please let me know if this solved your question. If it did, mark it as such using Thread tools.

[jakecutler]

Hi Evolta,

Thanks for the reply and the pick up about the missing exclamation marks.

Unfortunately that has not made the formula work.

Any other assistance would be much appreciated.

cheers

Jake