IF Formula Help
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.
Answers
-
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
Categories
Check out the Formula Handbook template!