Formula to pull from whichever field has value entered


#1

Hi, I’m looking for assistance in creating a formula that can show all our options for Inventory Location - Basically, at an Account, In House, or with a one-time buyer (that I don’t want to create an entire account for.)

If an Account is chosen (through a linked field), return that value. If a value is input in a separate Retail Buyer column (single line text), return that value. If both those fields are empty, return text “In House.”

The simple IF formula works to show Account or “In House”:

IF({Account}>"", {Account}, “In House”)

…but I can’t figure out how to turn this into a nested formula that will provide the third option of a typed in Retail Buyer.

Thanks for any help! I’ve managed to run all my company’s business through airtable, so I really hate when I can’t figure out one little thing :weary::grimacing:


#2

It’s pretty easy :stuck_out_tongue_winking_eye:


#3

Oh, I’ve tried to write a nested formula, trust me :upside_down_face: I have some pretty complicated ones elsewhere in my database. I just can’t seem to get this one to work!


#4

Something along these lines should work:

IF(
    {Account}!='',
    {Account},
    IF(
        {Retail Buyer}!='',
        {Retail Buyer},
        'In House'
        )
    )

You may want to use {Account}!=BLANK(), depending on whether you use a Lookup or Rollup field to access that data. My understanding is that a Lookup returns an array rather than a string, and I have had formulas fail when they attempted to compare an empty string ('') to a BLANK() array. (That said, at other times '' seems to equate to BLANK() without issue, so perhaps this has been corrected.) This can be difficult to diagnose in cases where a Rollup is later changed to a Lookup.


#5

That worked! Thank you so much!