Skip to main content
Question

Formula to Return Second-Largest Value in Set of Fields

  • December 7, 2025
  • 2 replies
  • 31 views
Anna11
Forum|alt.badge.img+13

I want to create a formula to select the SECOND-LARGEST value among five different categories. I’ve already created one to select the LARGEST value (called “Category 1), and then my thought was to exclude that category and return the max of the remaining four fields. With the help of Airtable’s AI, I got this formula:

IF(
  {Category 1} = "Connections",
    SWITCH(
      MAX({Events}, {Programs}, {Support}, {Marketing}),
      {Events}, "Events",
      {Programs}, "Programs",
      {Support}, "Support",
      {Marketing}, "Marketing"
    ),
  IF(
    {Category 1} = "Events",
      SWITCH(
        MAX({Connections}, {Programs}, {Support}, {Marketing}),
        {Connections}, "Connections",
        {Programs}, "Programs",
        {Support}, "Support",
        {Marketing}, "Marketing"
      ),
  IF(
    {Category 1} = "Programs",
      SWITCH(
        MAX({Connections}, {Events}, {Support}, {Marketing}),
        {Connections}, "Connections",
        {Events}, "Events",
        {Support}, "Support",
        {Marketing}, "Marketing"
      ),
  IF(
    {Category 1} = "Support",
      SWITCH(
        MAX({Connections}, {Events}, {Programs}, {Marketing}),
        {Connections}, "Connections",
        {Events}, "Events",
        {Programs}, "Programs",
        {Marketing}, "Marketing"
      ),
  IF(
    {Category 1} = "Marketing",
      SWITCH(
        MAX({Connections}, {Events}, {Programs}, {Support}),
        {Connections}, "Connections",
        {Events}, "Events",
        {Programs}, "Programs",
        {Support}, "Support"
      ),
    BLANK()
  )))))

Unfortunately, this returns the error “Sorry, there was a problem creating this field. Invalid formula. Please check your formula text.”

ChatGPT suggested the following edit:

SWITCH(
  {Category 1},

  "Connections",
    SWITCH(
      MAX({Events}, {Programs}, {Support}, {Marketing}),
      {Events}, "Events",
      {Programs}, "Programs",
      {Support}, "Support",
      {Marketing}, "Marketing"
    ),

  "Events",
    SWITCH(
      MAX({Connections}, {Programs}, {Support}, {Marketing}),
      {Connections}, "Connections",
      {Programs}, "Programs",
      {Support}, "Support",
      {Marketing}, "Marketing"
    ),

  "Programs",
    SWITCH(
      MAX({Connections}, {Events}, {Support}, {Marketing}),
      {Connections}, "Connections",
      {Events}, "Events",
      {Support}, "Support",
      {Marketing}, "Marketing"
    ),

  "Support",
    SWITCH(
      MAX({Connections}, {Events}, {Programs}, {Marketing}),
      {Connections}, "Connections",
      {Events}, "Events",
      {Programs}, "Programs",
      {Marketing}, "Marketing"
    ),

  "Marketing",
    SWITCH(
      MAX({Connections}, {Events}, {Programs}, {Support}),
      {Connections}, "Connections",
      {Events}, "Events",
      {Programs}, "Programs",
      {Support}, "Support"
    ),

  BLANK()
)

This too returns the error “Sorry, there was a problem creating this field. Invalid formula. Please check your formula text.”

I don’t believe either formula has syntax or field name errors. Any suggestions (other than switching to Python)?

EDIT TO ADD: Upon further research, I think the first formula above doesn’t work because there are too many nested IF statements. My workaround solution is to create a separate formula field for each possible value in Category 1, then concatenate the five fields into a sixth field. It’s VERY inelegant but works. Would still love to hear if anyone can think of something better!

    2 replies

    S
    Forum|alt.badge.img+24
    • Sho
    • Inspiring
    • December 8, 2025

    Hello Anna11

    The SWITCH function cannot handle dynamic values in arguments other than the first one.

    For example, using this formula allows you to detect which number of columns it is.
    Changing the SWITCH pattern arguments to correspond to this number should make it work.

    SEARCH(
      "@",
      REGEX_REPLACE(
        REGEX_REPLACE(
          "#"&{Connections}&"#,#"&{Event}&"#,#"&{Programs}&"#",
          "#"&{MAX}&"#",
          "#@#"
        ),
        "[^@,]",
        ""
      )
    )

     

     

    TheTimeSavingCo
    Forum|alt.badge.img+31

    Does this look right? 


     

    IF(
      AND(
        {Category 1} != "Marketing",
        {Marketing} =
          MAX(
            IF({Category 1} != "Connections", {Connections}),
            IF({Category 1} != "Events", {Events}),
            IF({Category 1} != "Programs", {Programs}),
            IF({Category 1} != "Support", {Support}),
            IF({Category 1} != "Marketing", {Marketing})
          )
      ),
      "Marketing",
      IF(
        AND(
          {Category 1} != "Support",
          {Support} =
            MAX(
              IF({Category 1} != "Connections", {Connections}),
              IF({Category 1} != "Events", {Events}),
              IF({Category 1} != "Programs", {Programs}),
              IF({Category 1} != "Support", {Support}),
              IF({Category 1} != "Marketing", {Marketing})
            )
        ),
        "Support",
        IF(
          AND(
            {Category 1} != "Programs",
            {Programs} =
              MAX(
                IF({Category 1} != "Connections", {Connections}),
                IF({Category 1} != "Events", {Events}),
                IF({Category 1} != "Programs", {Programs}),
                IF({Category 1} != "Support", {Support}),
                IF({Category 1} != "Marketing", {Marketing})
              )
          ),
          "Programs",
          IF(
            AND(
              {Category 1} != "Events",
              {Events} =
                MAX(
                  IF({Category 1} != "Connections", {Connections}),
                  IF({Category 1} != "Events", {Events}),
                  IF({Category 1} != "Programs", {Programs}),
                  IF({Category 1} != "Support", {Support}),
                  IF({Category 1} != "Marketing", {Marketing})
                )
            ),
            "Events",
            "Connections"
          )
        )
      )
    )

     

    Adam, Airtable Consultant - thetimesaving.company
    Terms & ConditionsAccessibility statement