Search code examples
google-sheetsspreadsheetarray-formulas

Google spreadsheet query execute for entire column

I tried VLOOKUP function for two separate Google spreadsheets but it doesn't support, So I figure it out my required function with a QUERY using a ImportRange function.

Here it is:

=QUERY( ImportRange( "sheet key" , "Course Data!A2:I4" ) , "select Col2 where Col1 = '" & E2 & "' limit 1" )

This is execute well for selected cell, it return value from the second spreadsheet according to the value of the current sheet E2 cell.

What I want to achieve is run this query for entire column (E3, E4, E5...)

I am new to Google Spreadsheets and saw something on internet with ARRAYFORMULA. But I couldn't apply it here.

Solution

  • I was wrong ! VLOOKUP supports for two different spreadsheets.

    This is work fine:

    =ARRAYFORMULA(IFERROR(Vlookup(E2:E,ImportRange( "sheets key (url in new sheets)" , "Course Data!A2:I4" ) ,2,False)))

    Cheers!