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

[Justin_Hall]

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!

[kuovonne · Accepted solution]

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:

[Justin_Hall]

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!