Find a column value based on the value of a formula

I am struggling with the following: I have a named formula ('actual_wind_direction)
I have a table called WindDirectionNames with, among others, the columns name_short and direction_to.
I am trying to retun (to a text area) the short name value from the row that holds the next larger value that I am passing to the formula (i.e. actual_wind_direction = 23, it should return ‘NNO’
Anyone any ideas? In Excel I would use vlookup with the TRUE option, but I not getting it to work in Coda. The part that I am not getting to work is the equivalent of TRUE option - exact values are being found with Lookup(), but not the in between values.

If I understand what you are looking for, maybe this formula will help:
This should filter the table for the rows where the value of the direction_to column is larger than the value of the ‘actual_wind_direction’ formula, then take the row with the minimal value and return the value of the name_short column in that row.

Hello Asaf_Dafna - thank you. This is one solution to get me the result I needed - I tweaked it a little bit and came up with:
WindDirectionNames.filter(direction_to > actuele_windrichting and direction_from < actuele_windrichting)

Off topic 1: how do you get to display the code (as you showed in your example with the and ()?
Off topic 2: is there a way to refresh a certain part of the page with a certain interval or upon changing underlying information: in my page I am picking up AccuWeather info, but it does not refresh until I hit F5, which refreshes the entire page, and I do not really want it to work that way.

Thanks again and greetings,

Dear @joost_mineur,

In addition of the great support of @Asaf_Dafna

Topic1: If you have a column name existing of more not connected words you can use “[ ]” to make it for the system look like one word . The “()” are actual part of the formula language, like in “min()”

Topic2: This has to do with the weather pack that on a free account only refreshes manual as described here:

Hello Jean Pierre,
OK - understood - both topics.
I am on a pro pack (and I will switch to the team pack soon because I need more than 500 automations per month), but I was not aware of the one hour update limitation. Can you think of a different way of importing some weather info: for what we are doing the one hour interval is a bit to low since we want to store actual information with our records (on active days up to 16 records per hour. Manually refreshing does not do the trick either - the information does not update until an hour has passed. Is there a not to complex way to ‘grap’ it from the internet somehow?

Dear @joost_mineur,

I don’t have experience with your specific use case.

It looks like that you will need to do some research if “Zapier” or “Integromat” can offer you this kind of information and most probably at what cost. I assume that also the information of the weather stations comes at a price. ( like on my mobile, where I use “Windy”)

True - I will look into that. Some cost is OK. We are going to operate our own internet connected weather station and I would like to find a way to connect it to Coda. We’ll see, it is not the most important project on my list, but I would like to make it work. We’ll see.

If you have some coding knowledge you can try using Google Apps Script instead of Zapier - it’s free and can run in whatever intervals you want.

My coding is a bit rusty (actually old style), but I will dive into this option.
Thanks for your suggestion.

1 Like