Can a value in a column be returned if that column is not blank?

jjesmith

Hello, I need a formula that looks at one entire column. If column is not blank, return the value in that column. There will always only be one value in the column but it may not always be in a specific row.

I started with the formula below, but I'm getting invalid operation. I thought [Last Name]:[Last Name] would look at the whole column. I think the error may be because the return value is included in what it is searching for.

The value I want to return could be in row 1 or 2 so maybe I need to add an And statement?

=IF([Last Name]:[Last Name] <> "", [Last Name]$1)

Paul Newcome

Try this:

=JOIN(COLLECT([Last Name]:[Last Name], [Last Name]:[Last Name], @cell <> ""))

jjesmith

That worked great.

Follow up question. Is there a way to combine that with an IF statement?

IF the JOIN/COLLECT returns X, enter X into the cell, IF the JOIN/COLLECT returns Y, enter Y into the cell OTHERWISE return the JOIN/COLLECT from Column Z.

I had this IF statement but it references specific rows so I'd like it to look at the column instead. When the answer is Other then it will Join/Collect a different column. Is that possible?

=IF([Work Location]$3 = "WFH", [Work Location]$3, IF([Work Location]$3 = "Bellevue Office", "Bellevue Office", IF([Work Location]$3 = "Spokane Office", "Spokane Office", IF([Work Location]$3 = "Carlsbad Office", "Carlsbad Office", IF([Work Location]$3 = "Other", Inputs51)))))

Paul Newcome

I'm not sure I follow. Are you able to post a screenshot of your sheet (it can be blank) so we can see the structure for reference?

jjesmith

Sorry, yes.

I have five options to a question Work Location (WFH, Bellevue, Spokane, Carlsbad, Other). Ideally it would be one formula where one of these options, returns another cell in the same sheet. For ease, let's just say there are two options: Bellevue Office and WFH.

1) If the Work Location cell (after the Join/Collect is performed) is "Bellevue Office", then

Return "Bellevue Office" into another cell on same sheet:

2) If the Work Location cell (after the Join/Collect is performed) is "WFH", then

collect and return the Mailing Address from a cell on the same sheet,

and put it here:

Paul Newcome

It looks like you want the output in two different cells (depending on the result of the JOIN/COLLECT)? In that case you would need two separate formulas. One for each cell.

If it is "Bellevue Office":

=IF([Work Location]2 = "Bellevue Office", "Bellevue Office")

If it is "WFH":

IF([Work Location]2 = "WFH", [Mailing Address]1)

Of course each of the cell references would need to be updated to reflect the appropriate row.