Complex Multi-Sheet Vlookup for Cross Workbook Reference

Using this formula to lookup my value across multiple worksheets and return the reference once found. Since I found this formula online, I'm not exactly sure how I can edit to incorporate the function across a different workbook. I've also heard that some sort of Index Match (besides the one in this formula) could be a better use, but I'm the type that defaults to vlookup so much, I wouldn't know how.

Quick explanation of this one: cycles through sheets until the reference and value are found then marks a "1" for the sheet with the value and "0" for the other sheets, allowing the function to be versatile in the array section of the vlookup.

Please Login or Register  to view this content.

I'm guessing the answer is to add the title of the workbook in where there is a

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

but I cannot get it to work.

Any guidance is much appreciated!

You're correct that you can add the title of the other workbook:
INDIRECT("'[otherfile.xlsx]worksheet name'!$A$1")

But Indirect doesn't work with closed files, so you need to keep the other workbook open.
Also, you seem to be using a named range 'SheetList' so be careful that you're not trying to reference it in the other workbook.

I have been trying in vain to create a dashboard based on numbers from other published monthly Excel leaderboards for our team.
I don't have access to the raw data from which the leaderboards are derived, but I can see all of the rendered and interpreted data as it relates to our team's performance metrics.
So far, I have been attempting to use the below formula, but am getting a "There's a problem with this formula" error that I am still unable to resolve:

=INDEX(" ' http://intranetlocation/[ " & $C4 & "_" & D$4 "_LEADERBOARD.xlsx]" & D$2 & " ' !$B$5:$V$32",
MATCH("OUR TEAM"," ' http://intranetlocation/[ " & $C4 & "_" & D$4 "_LEADERBOARD.xlsx] " & D$2 & " ' !$B$5:$B$32" ,0),
MATCH(E$3," ' http://intranetlocation/[ " & $C4 & "_" & D$4 "_LEADERBOARD.xlsx] " & D$2 & " ' !$B$5:$V$5" ,0))

where

C column = reference year of leaderboard workbook title
D column = reference month of leaderboard workbook title
row 2 = helper line for tab name(s) in outside workbook(s)
row 3 = column with desired data

The bold section is where Excel is highlighting my error.

What am I missing in this syntax?

Is this too complicated with outside book and tab name references to employ for a single formula??

You're missing some & signs.

Please also note that your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.