Hey everyone! I’m working on an Airtable project and I’m stuck. I’ve got a table with 17 columns of data. What I want to do is find the five highest values across all these columns and display them in a new table.
I know how to use the MAX formula to get the highest value, but that’s not enough. I need something similar to Excel’s LARGE function, but I can’t find an equivalent in Airtable.
I managed to do this in PostgreSQL with a complex query using cross join lateral and row_number, but I’m lost on how to achieve the same result in Airtable.
Has anyone tackled a similar problem before? Any tips or formulas that could help me out? I’d really appreciate any guidance on this! Thanks in advance for your help!
Having worked extensively with Airtable, I can say this is indeed a challenging task. One approach you might consider is using a script in the Scripting app. This would allow you to loop through all columns, collect values, sort them, and then output the top 5.
If you’re not comfortable with scripting, another option is to create a series of rollup fields. Each rollup could find the max of a subset of columns, and then you could use a formula field to compare these rollups and extract the overall top 5.
Both methods require some setup, but they’re more scalable than trying to handle all 17 columns in a single formula. The script option is more flexible if your table structure might change in the future.
I’ve faced a similar challenge in Airtable before, and it’s definitely tricky without a direct LARGE equivalent. Here’s a workaround I found effective:
Create a new field that concatenates all 17 columns, separated by commas. Then use SPLIT() to turn this into an array. From there, you can use ARRAYUNIQUE() to remove duplicates, then SORT() to order the values, and finally SLICE() to get the top 5.
It’s not the most elegant solution, but it works. The formula would look something like this: