vlookup(importrange) function doesn't find data from query result table in another spreadsheet

1
votes

I've two teams. Each of those team has its own spreadsheet file they work with. I've created a new spreadsheet document that uses query(importrange) function to create a single table of all results from each team's spreadsheets.

The idea is to then use the vlookup(importrange) function in yet ANOTHER spreadsheet document to deliver specific query results. i.e. by vlookup'ing email address from the query database, import other relevant information to that email address.

I've been using this logic for a few months now and everything seemed to work fine. The only difference is that my past experience was with query result table that contained only 1 column of data.

Now, when the query result table contains 7-9 columns, this no longer seems to work.

I've concluded, that only the first column is somehow visible for the vlookup(importrange) function, while other columns seem to be invisible.

The Query result table formula:

=QUERY({IMPORTRANGE("Team A","In Progress!A:M");IMPORTRANGE("Team B","In Progress!A:M")},"where Col1 is not null",1)

The formula on another spreadsheet that should act as a verifier/checker:

=IFerror(arrayformula(VLOOKUP(D3,IMPORTRANGE("the query result's sheet","In Progress!A:G"),{1,4,6,7},0)),"-")

I really hope the problem is described clearly. I expect vlookup(importrange) to import data from query result table in another spreadsheet document.

1
google-sheetsarray-formulasgoogle-sheets-querygs-vlookupimportrange
Team 1 work: docs.google.com/spreadsheets/d/… Team 2 work: docs.google.com/spreadsheets/d/… The Data sheet: docs.google.com/spreadsheets/d/… The sheet for personal use to see what's happening for a given website: docs.google.com/spreadsheets/d/… The goal is to vlookup a domain name and get results from data sheet – Simon Adams

1 Answers

0
votes
=ARRAYFORMULA(IF(LEN(A3:A), IFERROR(VLOOKUP(A3:A, 
 QUERY(IMPORTRANGE("15xOcSh39zZgn1829DOYF5hB6WeuaTO81pyi6Ek3R-Gw", "Sheet1!A:H"), 
 "select Col2,Col1,Col3,Col4,Col5,Col6,Col7", 0), {2, 5, 6, 7}, 0), "-"), ))

0