0

I have 2 sheets

people sheet has 

[company    first_name  last_name   full_name   address_line1   address_city    address_state   address_zip address_country telephone   email   order_id    order_date  order_time  processor   order_type  order_currency  commission  campaign_id]

sales sheet has

[DATE   PROSPECT NAME   EMAIL ADDRESS   CONTACT METHOD  PRODUCT RESULT  NOTES ]

How do I make the report to generate when people.email = sales.EMAIL ADDRESS and display the records only if they match (Not all of sales sheet)

Report fields are 

full_name
EMAIL ADDRESS
PRODUCT
commission
Code Guy
  • 3,059
  • 2
  • 30
  • 74

1 Answers1

3

Sample File

Please try to use basic formula for joining sheets + query to skip some rows.

Basic 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!A14:A<>"")

The formula you need:

=QUERY(FILTER({Sales!B14:B,Sales!C14:C,Sales!E14:E, IFERROR(vlookup(Sales!E14:E,{People!B2:B,People!D2:D},2,), "do not show"), Sales!F14:F},Sales!A14:A<>""),"select * where Col4 <> 'do not show'")

How it works

It uses iferror(expression, "do not show") to replace #N/A error with the text "do not show".

Then it uses query to hide rows with value "do not show":

"select * where Col4 <> 'do not show'"

Basic formula and the question is here

Notes about usage:

  1. Sales!B14:B,Sales!C14:C,Sales!E14:E are some columns you need to select first
  2. IFERROR(vlookup(Sales!E14:E,{People!B2:B,People!D2:D},2,) will return column from "People", it is column People!D2:D. Please note that columns Sales!E14:E and People!B2:B are both containing a key, in the sample it is e-mail list.
  3. Sales!F14:F is a column to return next.
  4. filter condition Sales!A14:A<>"" is to skip empty rows from original sheet. You may also add other conditions into a filter.
  5. query part will shorten the report and skip values where keys (emails) do not match. You may add other conditions into a query.

Note. Query language in Google Sheets does not have joins, and provided solution is a workaround. It shows how to make a join with a help of vlookup function in Sheets.

  • The provided formula gathers rows from sheet "Sales" and makes vlookup to sheet "People". You also may want to make vice-versa report: collect data from "People" and 'vlookup` data from "Sales".

"Vice-versa" report:

=QUERY(FILTER({vlookup(People!B2:B ,{Sales!E14:E, Sales!B14:B, Sales!C14:C, Sales!E14:E},{2, 3, 4},), People!B2:B, IFERROR(vlookup(People!B2:B ,{Sales!E14:E, Sales!F14:F},2,),100500)},People!A2:A<>""),"select * where Col5 <> 100500")

note! In this formula used numeric value 100500 to skip values because query works with the single data type (number or text).

Please comment here if you have any questions about how it works.

Community
  • 1
  • 1
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • 1
    I've added some notes to the answer. Query language in Google Sheets does not have joins, and provided solution is a workaround. It shows how to make a join with a help of `vlookup` function in Sheets. Hope this helps. – Max Makhrov Jan 09 '18 at 13:28
  • Added the formula to make a report from sheet "People" as a main sheet (where the unique ID's are stored. – Max Makhrov Jan 09 '18 at 13:53