Question

Zap to match multiple cells between two Google Sheets and add results to 3rd Sheet

  • 10 November 2022
  • 11 replies
  • 402 views

I have 3 sheets in Google Sheets. I need to take 3 columns (Columns B-D) from the 1st sheet and search the 2nd sheet to see if there are any exact matches. If so, the a portion of the row (Columns B, C, D, F, G) from the 1st sheet should be added to the 3rd sheet/results sheet.

 

Example:

Sheet 1
Columns B-D would be used to see if there is an exact match in Sheet 2.
Sheet 2
​​​

 

Sheet 3 - Results from Zap

 


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

11 replies

Userlevel 7
Badge +14

Hi @clark1981 

Can you please clarify your question?

What should happen if there are multiple matches?

Perhaps outline a specific example so we can better understand the inputs and desired outputs.

I’m sorry. I thought the pics were further detail. If there are multiple matches between Sheets 1 and 2, then the zap should post the specific results of the match (chosen cells from sheet 1) to sheet 3...as shown in the 3rd pic I posted. 

The row with Kelly Smith matched which should cause chosen columns of info from Sheet 1 to post to Sheet 3.

I hope that makes more sense.

Userlevel 7
Badge +14

@clark1981 
You’ll probably need to add another column that uses a formula to concatenate the desired column values into 1 value that can then be searched.

You’ll want to use one of these Zap actions to find matches.

 

Ok. Thanks. I did look at the Lookup Spreadsheet Rows, but wasn’t clear on what it was asking, but now after a second look, I see it does let you search and match by Lookup Column and Supporting Lookup Column. So the Zap will trigger only on rows that match both columns. (An example column might be something like Email Address or Phone Number)...which is great, but I wish I could add a 3rd column. 

Do you think adding a 2nd Lookup Spreadsheet Rows would take care of the search within a 3rd column?

Also, how do I get it to look at another Sheet for the match? It seems as if that it would only be looking for a match within the same sheet.

Userlevel 7
Badge +14

@clark1981 

Q: Do you think adding a 2nd Lookup Spreadsheet Rows would take care of the search within a 3rd column?

A: No

You’ll probably need to add another column that uses a formula to concatenate the desired column values into 1 value that can then be searched.

Userlevel 7
Badge +14

@clark1981 

Q: Also, how do I get it to look at another Sheet for the match? It seems as if that it would only be looking for a match within the same sheet.

A: In the GSheet Zap step configuration, you can select the Spreadsheet and Worksheet to use.

So its two separate actions? One lookup for one sheet and one lookup for the other?

Then a 3rd action to paste the match into the 3rd sheet?

Userlevel 7
Badge +14

@clark1981 

If you want to do lookups on 2 different GSheet tabs (worksheets), then yes that will require 2 different Zap steps.

When in doubt, test it out!

I can’t make it work. The test seems to create a new row on the 3rd sheet (the results sheet), but it doesn’t add all of the fields/cells I’ve requested. It adds only the cells selected and mapped from the first sheet and not the cells selected and mapped from the second sheet.

It also doesn’t look like its matching columns from the first sheet to the columns on the 2nd sheet. 

I even published the Zap and and started the whole process by adding a row to trigger the zap. I’m not sure where I’m going wrong.

 

 

Userlevel 7
Badge +14

@clark1981 

You’d need to provide detailed screenshots with how each of your Zap steps is configured in order for us to have enough context.

Userlevel 4
Badge +1

Hi there!

As we’re currently working through this in a support request, I’m more than happy to follow up there. However, I’d like to share a potential solution for readers:

  • Create a column X in sheet 1 and sheet 2
  • Use a formula in X2:
    =ArrayFormula(iferror(B2:B & " " & C2:C & " " & D2:D))
    (see also this screenshot)
  • Add a Lookup Spreadsheet Row in Google Sheets step to search for a concatenated value in sheet 2, based on the concatenated value in sheet 1.

Hope that helps!