I'm working with a Google Spreadsheet that's pulling data from another sheet if certain conditions are met. Well, at least that's what it should be doing—instead, I'm getting "No matches are found in FILTER evaluation."
The function is:
=filter(importRange("https://docs.google.com/spreadsheets/d/1Z_7hl4uEc-an2rOUgOd_zYhCeb_QNIZopahJqBYooRg/edit#gid=0", "Sheet1!R2:R5000"), SEARCH( A3 , index(importRange("https://docs.google.com/spreadsheets/d/1Z_7hl4uEc-an2rOUgOd_zYhCeb_QNIZopahJqBYooRg/edit#gid=0", "Sheet1!V2:V5000")) ) )
I've tried it with a variety of row and column parameters for the index() function. I've also tried adding * to the beginning and end of the search term in A3, in case that's the issue. I've also tried putting quotes around the value in A3.
What am I missing? Sample spreadsheet is here.
I can't find a reference at the moment, but there is a known issue associated with the fact that the newest version of Sheets requires that you explicitly allow access to the other sheet via ImportRange. The issue is, when the ImportRange is nested, it doesn't give the opportunity to allow access - it will just return a #REF error inside your formula.
The work around is to just invoke the ImportRange by itself first (you could use a smaller range):
=ImportRange("https://docs.google.com/spreadsheets/d/abcdefg","Sheet1!R2")
then "Allow access" when prompted; then nest it in your formula.
As an aside, it is advisable to use ImportRange as few times as possible, so in your case it might be better to use QUERY:
=QUERY(ImportRange("https://docs.google.com/spreadsheets/d/abcdefg","Sheet1!R2:V5000"),"select Col1 where Col5 contains '"&A3&"'",0)