IF Formula Help

Beecher2019

Hello!

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

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.