How to extract top 5 highest values from multiple columns in Airtable

Other Questions Airtable
1

I’m working with an Airtable database that has a bunch of numeric columns (about 17 of them). I need to find the 5 largest values from all these columns combined and display them in a specific format.

Right now I can only get the maximum value using the MAX function, but I need something similar to Excel’s LARGE function to get the top 5 values. I tried different approaches but can’t figure out how to do this in Airtable.

In other databases I would use something like this approach:

SELECT data.*, results.*
FROM data_table CROSS JOIN LATERAL
     (SELECT max(vals.value) FILTER (WHERE rank_num = 1) AS top_1,
             max(vals.value) FILTER (WHERE rank_num = 2) AS top_2,
             max(vals.value) FILTER (WHERE rank_num = 3) AS top_3,
             max(vals.value) FILTER (WHERE rank_num = 4) AS top_4,
             max(vals.value) FILTER (WHERE rank_num = 5) AS top_5
      FROM (SELECT vals.value, row_number() OVER (ORDER BY value DESC) AS rank_num
            FROM (VALUES (field_1), (field_2), (field_3)...
                 ) vals(value)
           ) vals
      ) results;

But I’m stuck on how to implement something similar in Airtable. Any suggestions on how to achieve this?

2

Airtable formulas get messy fast when you’re ranking beyond the top 2-3 values. I tried cascading formula fields with complex IF statements to exclude previous maximums, but it became unreadable and error-prone quickly.

What worked better was an Airtable script that handles all numeric columns programmatically. The script grabs all values, sorts them descending, then fills dedicated fields with your top 5 results. You can set it to auto-run through Airtable’s automation when records change.

Downside? You’ll need scripting enabled in your workspace. But it’s way cleaner than nesting dozens of conditionals in formula fields.

3

Airtable doesn’t have Excel’s LARGE function, but here’s a workaround using formula fields.

First, create a formula field to find your highest value:

MAX(
  MAX(Field1, Field2, Field3, Field4, Field5),
  MAX(Field6, Field7, Field8, Field9, Field10),
  MAX(Field11, Field12, Field13, Field14, Field15),
  MAX(Field16, Field17)
)

For the second highest, you’ll need another formula that excludes the top value:

MAX(
  IF(Field1 = [Top1Field], 0, Field1),
  IF(Field2 = [Top1Field], 0, Field2),
  // continue for all fields
)

But honestly? This gets ugly fast with 17 columns. I’ve been down this road before and ended up using Airtable’s API - pull the data into a script, handle the ranking there, then push the top 5 back to new fields.

Better yet, restructure your data. Instead of 17 columns, use a linked table with value/type pairs. Makes ranking way cleaner.

4

Been wrestling with similar ranking problems for years. The formula approach everyone mentions works but hits a wall around 5-7 columns.

I’d skip the heavy lifting in Airtable and use something like Latenode to create a simple workflow. Pull your records via API, process the ranking logic (way cleaner than nested formulas), then push the top 5 back to dedicated fields.

Ran into this exact issue last month with financial data across 12 columns. Tried the cascading MAX approach first but debugging those formulas was a nightmare when values changed.

The automation route saves you from formula hell and scales better when your column count grows. Plus you can add other logic like handling ties or filtering criteria without rewriting complex nested statements.

This video shows solid techniques for maintaining ranks that might help with your setup.

If you’re stuck with formula fields only, create 5 separate fields and use progressively complex IF statements to exclude previous winners. But honestly the API route will save your sanity.

5

airtable’s not great for complex ranking. i’d use zapier or make to push the data into google sheets - you can use the large function there without issues. way easier than fighting with nested formulas that break every time you add records.

6

Navigating Airtable’s formula limitations can indeed be frustrating, especially when you’re used to more robust SQL capabilities. I faced similar issues transitioning from PostgreSQL. To manage the top values efficiently, you’d typically create multiple helper fields that compute the maximum values while excluding those already considered. This involves using nested IF statements which can quickly become cumbersome, especially with 17 columns in play. For my own use case, I opted to export the data to CSV, process it through a quick Python script to do the ranking, and then re-import the results. It’s not only faster but also streamlines the process considerably. Alternatively, using JavaScript with Airtable’s automations to write a looping script might work well, provided you’re comfortable with coding. This could simplify the workflow without the need for external processing.