# return field name from the MAX of fields in a row?

Topic Labels: Formulas
Solved
730 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Let's say we have a row with a product & prices for that product at four regional stores:

Chocolate Bar
Store A: \$1
Store B: \$3
Store C: \$2
Store 😧 \$5

I'd like a formula that would look across the store prices, determine the highest price, and then return the name of that field. I want a function that would return "Store D" in the example above.

MAX(Store A, Store B, Store C, Store D) will help me identify the highest price, but it returns the \$5 value, not the field name Store D. How do I get something like MAX returning the winning field name?

Thanks!

1 Solution

Accepted Solutions
18 - Pluto

I'm guessing that you come from a spreadsheet background.

As you add more stores, your table will get wider. However, when designing a database for this type of data, it would be better to structure the data with linked tables. One table for [Products], another for [Stores], and a junction table for [Prices]. Then you could do a set of back-and-forth rollups to determine the max price and the store with the max price. The system would scale to lots of stores without having to add any fields or edit formulas.

But that's not what you asked. I think you want something like this, even though I still recommend having a different schema setup instead.

Here are the formula fields:

2 Replies 2
18 - Pluto

I'm guessing that you come from a spreadsheet background.

As you add more stores, your table will get wider. However, when designing a database for this type of data, it would be better to structure the data with linked tables. One table for [Products], another for [Stores], and a junction table for [Prices]. Then you could do a set of back-and-forth rollups to determine the max price and the store with the max price. The system would scale to lots of stores without having to add any fields or edit formulas.

But that's not what you asked. I think you want something like this, even though I still recommend having a different schema setup instead.

Here are the formula fields:

5 - Automation Enthusiast

Thanks Kuovonne!  I don't need this to scale for this particular case. Your pair of formulas works exactly as I'd like for the few fields I'm running this on. That's another time you've helped me with AirTable. Appreciate it!