I think I need something like this just not sure of the format of the formula.

@leadrouter 

Help article for Formatter > Match > Spreadsheet-Style Formula: https://zapier.com/help/create/format/understand-spreadsheet-style-formula-functions

This link is underneath the Formula filed in the screenshot you provided.

Sometimes the best way to figure something out, is to test it out.

Alternatively, Airtable has advanced search formulas and APIs for these type of uses cases.

https://support.airtable.com/hc/en-us/articles/218151657

Thanks I read that first and searched Spreadsheet-Style Formula: and have not found anything really comparable.

When returning a row from a spreadsheet. (probably something that 1 million people would like to do)

This is not helpful.

• (1 + 2) / 4 returns 0.75
• POW(2, 4) returns 16
• IF(ISEVEN(2), 100, 200) returns 100
• AND(ISODD(1), ISEVEN(2)) returns TRUE
• ROUNDUP(1234.567 * 106%, 2) returns 1308.65
• IF(1 < 2, IF(3 = 3, "you did it!", 20), 30) returns you did it!
• 100 * (VALUE("123" & "." & "01") + 7) returns 13001

nor is this 

• MIN(number, inumber, …]): Returns the smallest value from a list of numbers

But if I jump to Google sheets 

it takes 30 seconds to figure out how to this =MIN(D:D)

Just some sort of practical examples of lookup up and working with the result would be helpful.

@leadrouter 

Were you able to figure it out?

I can’t tell based on your last response.

no..getting nowhere… 

I am not understanding this 

I do not understand why this

Outputs this instead of 0

It actually does not seem to matter what I put into the Formula field the output is always the same…

=2. Rows Col$d: 1,0,5 * 100 does not change the output…

I am wondering if it does not consider the field to be a number?

@leadrouter 

An alternative option, would be to do this in your Zap.

Action: Delay - After Queue

Action: GSheets - Update Row (add a new tab to the GSheet that uses a QUERY or FILTER formula to isolate the desired data points)

@leadrouter 

Or try using this Formatter > Utilities > Line-item to Text as a Step before the Formatter > Numbers > Spreadsheet-style Formula to convert the returned line items into a comma separated list.

@leadrouter 

> I am wondering if it does not consider the field to be a number 

I think you’re right! Based off that screenshot it looks like we’re trying to format an array of numbers, but this is not easy to see in the Zap editor.

Can you try this:

1. Add a Formatter > Utilities > Line-item to Text action, and map the output of your Google Sheets step there (the “1,0,5”)

   1. This will turn that into a string of 1,0,5

2. Send that string ^ to the formatter step you have with the spreadsheet style formula. Based off that, it should return “1”

After that it sounds like want to reference the row that matches “Utah” and “1” - which I think should be doable with another Lookup Spreadsheet row action, using a two column search. 

Let me know if this helps!

Bingo that worked!

 

 

After that it sounds like want to reference the row that matches “Utah” and “1” - which I think should be doable with another Lookup Spreadsheet row action, using a two column search. 

 

 

Yes you are right. 

This is what I did 

One thing I am not sure of is what it will do when there are more then one match with the single row look up.

I tried changing it to the “Bottom up” did not make any difference when two matched… which means there is something else I am not understanding. With bottom up True or False it selected record 10

For me as long as I am not breaking it it does not really matter which one it selects but I am just concerned when it does not work as expected that I might be missing something.

I have one more question.

Is there a way to use a “Contains” lookup.

I would like to return any row that contains a string. 

For example the State field might be Utah, Florida, Texas.

I Ould like to search by Utah and return the row because it contains….

For states I could just create one record for each state. But that won't be possible when I get down to County or Zip.

Thanks