# Find column with highest value in row

Topic Labels: Formulas
442 4
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

I am trying to write a formula to return the name of the column for each rows highest value. Attached is what my table looks like. So for example the first row should return blue, and the second row should return purple.

Thank you!

4 Replies 4
6 - Interface Innovator

Hi Sareyh,

You could use MAX() and a bunch of nested IF statements. Not exactly elegant and there is likely a cleverer way, but it should work. Have you considered what you would want to happen if more than one row has the same highest value?

``````IF(
{Red} >= MAX({Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Red",
IF(
{Orange} >= MAX({Red}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Orange",
IF(
{Yellow} >= MAX({Red}, {Orange}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Yellow",
IF(
{Green} >= MAX({Red}, {Orange}, {Yellow}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Green",
IF(
{Blue} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Purple}, {Pink}, {Black/Brown}), "Blue",
IF(
{Purple} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Pink}, {Black/Brown}), "Purple",
IF(
{Pink} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Black/Brown}), "Pink",
IF(
{Black/Brown} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}), "Black/Brown",
BLANK()
))))))))``````

8 - Airtable Astronomer

Assuming you have the following columns:

Blue
Purple
Green

`IF(    AND({Blue} >= {Purple}, {Blue} >= {Green}),    "Blue",    IF(      AND({Purple} >= {Blue}, {Purple} >= {Green}),      "Purple",      "Green"  ))`
5 - Automation Enthusiast

Thank you! Would it be possible to also find the second most common color?

6 - Interface Innovator

Hi Sareyh,

It is possible to do this using formulas, but it is even more cumbersome than the approach to finding the highest value. I am not sure if you are using a free plan, but if you have access to workspace on Team or above I would suggest you solve this using a script. This approach is far easier to maintain should you need to add or remove colour fields.

An example would be:

``````// Find the highest & second highest values in your table. You need to add the name of your table here...
let table = base.getTable("Your Table Name");

// Load the records in the table
let query = await table.selectRecordsAsync();
let records = query.records;

// List of colour fields. You can add or remove fields here by following this pattern.
let colourFields = ['Red', 'Orange', 'Yellow', 'Green', 'Blue', 'Purple', 'Pink', 'Black/Brown'];

// Iterate through each record
for (let record of records) {
// Get the values of the colour fields for the current record
let colourValues = colourFields.map(colour => ({
name: colour,
value: record.getCellValue(colour) || 0
}));

// Sort the colour values in descending order by value
colourValues.sort((a, b) => b.value - a.value);

// Get the highest and second-highest colours
let highestColour = colourValues[0].name;
let secondHighestColour = colourValues[1].name;

// Update the record with the highest and second-highest colours
await table.updateRecordAsync(record.id, {
"Highest": highestColour,
"Second Highest": secondHighestColour
});
}``````

You can add this to a scripting block in the Extensions panel, editing the script to switch "Your Table Name" for the name of the table you are using. This script also assumes you have two fields, one called "Highest" and another called "Second Highest".