IF Formula Help



I'm looking for assistance with creating a formula to return a value on the New Hire Sheet pulling a result from the Offer Sheet. Specifically I'm looking for the following:

When the [Primary Column]@row = "Completed" on the Offer Sheet, I need a formula to return the "Candidate Name" from the Offer Sheet in the "Name" Column on the New Hire Sheet.

I don't have enough data to do a VLOOKUP and I've tried a few IF formula's which yield no success. The IF formula I was testing is below.

=IF({Offer Range 1} = "Completed", "Completed", {Offer Range 4})

Any assistance would be greatly appreciated.


  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    Hi. This may help.

    Imagine your Offer Sheet looks something like this:

    Note that I've added a column called [Row ID]. This is set up as an Auto-Number column in the Column Preferences.

    And your New Hire sheet looks something like this:

    Again, I've added a [Row ID] column as an Auto-Number column.

    In the Name column, use this formula:

    =IFERROR(INDEX(COLLECT({Offer Sheet Candidate Name}, {Offer Sheet Status}, "Completed", {Offer Sheet Row ID}, [Row ID]@row), 1), "")

    This will pull across the names of the candidates who have a "Completed" status.

    It may also be possible to accomplish this using a workflow, such as the copy a row or change a cell value when criteria are met options, but it's hard to say without seeing your sheet structure.

    Hope this is helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!