How do you insert the fieldname of the lowest value?

[FerryAtMM]

My issue is pretty simpel I think. How can I make a formula that checks for the lowest value in certain fields and inserts the name of the fieldname corresponding with the lowest value. Also, then, how can I insert the corresponding value in another field.

Kindly take notice of the two text fields in this example.

[Jeremy_Oglesby]

For the field set up you have in your screenshot there:

Make your “Lowest Buy Price At” field a formula field with this formula:

IF(AH<Babylon,"AH",IF(Babylon<AH,"Babylon",BLANK()))

If you have other vendor fields you need to check against, this will get more complicated.

Make your “Lowest Price” field a formula field with this formula:

MIN(AH,Babylon)

and be sure to change the formatting to “Currency”.

[FerryAtMM]

Jeremy_Oglesby:

If you have other vendor fields you need to check against, this will get more complicated.

This is actually going to be the case. Let’s say we have 3 more fields, how does it work then?

[Jeremy_Oglesby]

Well, the formula in “Lowest Price” is simple to add more fields to. Just keep adding field names separated by commas:

MIN(AH,Babylon,Vendor1,Vendor2,etc..)

For the “Lowest Buy Price At” field, you’ll need to do something like this:

IF(
   AND(AH < Babylon, AH < Vendor1, AH < Vendor2, etc..),
   "AH",
   IF(
      AND(Babylon < AH, Babylon < Vendor1, etc...),
      "Babylon",
      IF(etc...
         )
      )
   )

Just keep nesting IF() statements like that checking each vendor against each other vendor in an AND() statement. At the end of the last IF() statement, instead of adding another IF(), you’ll have the BLANK() to return blank if none of the statements are true. (Make sure all of your IF() statements are closed at the end - ie, closed parenthesis).

There may be a more efficient way to do that - if I come up with one, I’ll let you know.

[FerryAtMM]

Thanks Jeremy! That works.

If you come up with a more efficient way, I’ll be happy to know.

[FerryAtMM]

I came across another issue regarding this formula.

When the field is empty, it shows the name of that empty field. Sometimes I don’t want to put any values for a certain vendor for the item. Is it somehow possible to exclude empty fields in the formula?

Also notice that when I have two (or more) empty fields, it doesn’t show any information at all.

In case I have two vendors with the same price, it would be perfect if it can show me the name of both vendors in that field. However, I can imagine that this is technically hard (or even impossible).

Is it possible to fix this?

The formula:

IF(AND(Vendor1 < Vendor2, Vendor1 < Vendor3, Vendor1 < Vendor4, Vendor1 < Vendor5),"Vendor1",IF(AND(Vendor2 < Vendor1, Vendor2 < Vendor3, Vendor2 < Vendor4, Vendor2 < Vendor5),"Vendor2",IF(AND(Vendor3 < Vendor1, Vendor3 < Vendor2, Vendor3 < Vendor4, Vendor3 < Vendor5),"Vendor3",IF(AND(Vendor4 < Vendor1, Vendor4 < Vendor2, Vendor4 < Vendor3, Vendor4 < Vendor5),"Vendor4",IF(AND(Vendor5 < Vendor1, Vendor5 < Vendor2, Vendor5 < Vendor3, Vendor5 < Vendor4),"Vendor5",BLANK())))))

[Jeremy_Oglesby]

Hmm… ya, I don’t see a good way around that issue.

If you’re willing to rethink the way you organize this particular table, you could try something like this:

Make a record only for each ingredient sold by each vendor (eliminates the blanks):

Then you can Group the records on Ingredient, hide the Ingredient field, and then sort by Price Low -> High.

If you add a new record with the view like this, you’d add it to an Ingredient Group, which will auto fill the Ingredient field with the Ingredient of the Group you add a record to. You’ll have to reapply the sort however (Airtable does not automatically refresh the sort when new records are added).

[FerryAtMM]

Thanks you for thinking along with me! That indeed makes the lowest price visible, but for me it is important to know the name of the lowest vendor and corresponding price; for this reason:

On another tab where the ingredients are in, I want it to show the cheapest vendor // purchase price. At the recipe field I want it to show all the vendors needed for that particular recipe (in case I want to purchase at cheapest) and count up the buy price per recipe; which all is easily fixed then.

[W_Vann_Hall]

Here is a variant on the solution suggested by @Jeremy_Oglesby. It uses a one-to-many link from the [Ingredients] table to the [Vendors] table to store a vendor and price per ingredient, and then uses a combination of rollup and lookup fields to display the current lowest price for each ingredient and the vendor(s) offering that price. Setting row height to ‘extra tall’ allows one more easily to see which vendors currently offer each ingredient.

Airtable

🍳 Ingredient demo - Airtable

Explore the "🍳 Ingredient demo" base on Airtable.

[FerryAtMM]

Wonderful, many thanks!