How to join tables between the sheet and query in

Question

In Sheet2 I have the following columns with ~1000 rows

[ NAME      EMAIL-ADDRESS       PHONE       PRODUCT ]

In Sheet3 I have the following columns with 3000 rows

[ company   first_name  last_name   full_name   email   amount ]

I need to get the the result by joining sheets, that is

Sheet3 email = Sheet2 EMAIL ADDRESS

first_name last_name (from Sheet3), email address (from Sheet3), PRODUCT (from Sheet2), amount (from Sheet3)

How do I get this, its ok even if it is a custom function in Google Sheets

I tried with

=QUERY(Sheet2!A1:D3000, "Select B & ' ' & C WHERE B='" & Sheet3!$E2 & "'", 0)

Answer 1

Sample Sheet.

You may use filter + vlookup in a single formula:

=FILTER( {Sales!B14:B,Sales!C14:C,Sales!E14:E, vlookup(Sales!E14:E,{People!B2:B,People!D2:D},2,), Sales!F14:F},Sales!E14:E<>"")

FIlter will convert vlookup into ArrayFormula. This will return PRODUCT from sheet "People".

I renamed sheets to give them more meaningful names.

• "Sales" = "Sheet3"
• "People" = "Sheet2"