I’m trying to create a VLOOKUP formula that references an external Google Sheets file using the INDIRECT function. I want to make both the workbook reference and sheet name dynamic by pulling them from specific cells.
Here’s what I’m attempting but it’s not working:
=IFERROR(VLOOKUP($R$5,INDIRECT(B7"&"$H$4"!$AA:$BC"),15,FALSE),"No Match Found")
In my setup:
- Cell B7 contains the external workbook URL
- Cell H4 contains the sheet name I want to reference
- I’m trying to lookup data in columns AA through BC
I’ve experimented with different syntax combinations but can’t seem to get the INDIRECT function to properly combine the workbook link with the sheet name and range. The formula keeps returning errors. Has anyone successfully used INDIRECT this way with external sheets? What’s the correct syntax for combining these dynamic references?
Been wrestling with this exact issue for months. The syntax errors others mentioned are right, but here’s another gotcha - INDIRECT with external sheets only works if the target workbook’s already open in another tab or you’ve accessed it recently. Even with perfect syntax, you’ll get random #REF errors when Google’s cache expires. Learned this the hard way after building several dashboards that would randomly break. My workaround is using QUERY with IMPORTRANGE for dynamic external references. Something like =QUERY(IMPORTRANGE(B7,H4&"!AA:BC"),"SELECT * WHERE Col1 is not null") then do your lookup on that imported data. Takes longer to set up but actually works reliably in production.
Your formula has two problems: wrong concatenation syntax and missing quotes. You’re not properly quoting and connecting the cell references. But honestly, INDIRECT with external workbooks in Google Sheets is pretty unreliable - it fails randomly all the time. External references through INDIRECT don’t keep stable connections, especially when the external sheet isn’t open or permissions change. I’d use IMPORTRANGE instead - pull the data into your current sheet first, then do the VLOOKUP locally. Way more reliable and faster than depending on INDIRECT for external references.
you’re missing quotes around the concatenation - try =IFERROR(VLOOKUP($R$5,INDIRECT("'"&B7&"'!"&$H$4&":$AA:$BC"),15,FALSE),"No Match Found") but honestly INDIRECT with external sheets is super flaky in google sheets. sometimes it just breaks for no reason.