Hi I am trying to look up a row in Sheets that first is a text match. In this case a row that matches UTAH. And then of those 3 results pick the one with the lowest number. I have tried adding a formula in the supporting lookup but I am not getting anywhere. In the example below I would like to select the 8th row.
THANK YOU!
Best answer by Matt_BoydenBest answer by Matt_Boyden
> 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:
Add a Formatter > Utilities > Line-item to Text action, and map the output of your Google Sheets step there (the “1,0,5”)
This will turn that into a string of 1,0,5
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!
+14Help 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.75POW(2, 4) returns 16IF(ISEVEN(2), 100, 200) returns 100AND(ISODD(1), ISEVEN(2)) returns TRUEROUNDUP(1234.567 * 106%, 2) returns 1308.65IF(1 < 2, IF(3 = 3, "you did it!", 20), 30) returns you did it!100 * (VALUE("123" & "." & "01") + 7) returns 13001
nor is this
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.
+14Were 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?
+14An 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)
+14Or 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.
> 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:
Add a Formatter > Utilities > Line-item to Text action, and map the output of your Google Sheets step there (the “1,0,5”)
This will turn that into a string of 1,0,5
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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
