IF Function

Sue08

I just want to return the values of Completed or Not completed.

If the column "Daily" = "#NO MATCH" return not completed otherwise return Completed

Im using

however Im not gettting "Not completed" when the Daily column is "#NO MATCH"

Thank you very much

Christian Graf

I'm not sure that you can use an error like that in an if statement. Try changing the formula in the daily column to =IFERROR(formula,"").

Then for your weekly column you can use the formula =IF(ISBLANK(Daily@row) = 0, "Completed", Not Completed")

Sue08

Hi Christian

Many thanks.

What Im trying to do is to identify which country has not completed the smartsheet form survey. The responses are on sheet "IDP countries process review survey". On my calculation sheet I have listed all of the countries I expect to have completed the survey. I used a vlookup to check the countries listed on the survey sheet against the list of countries on my calcuation sheet. My desired outcome is to have a column with 'completed' and 'not completed' against each country I have listed on the calculation sheet.

I hope this make sense

Sheet 1 - form survey results

Sheet 2 - calcuation sheet

Kelly Moore

Hey @Sue08

@Christian Graf 's approach will work- you could even change Christian's IFERROR slightly to replace the "" to give you "No Match", which your IF statement could be adjusted to find. That being said, try adjusting your current IF statement to this:

=IF(ISERROR(Daily@row), "Not completed", "Completed")

This will find errors.

Kelly

Sue08

Hi

I cant get this to work either. The IFERROR statement Im unsure about

Its returning "complete" for all countries and not identifying the countries which arent on the edit reference sheet

Kelly Moore

Hey Sue

Let's back up a bit. Looking at your screenshots, it appears you have other columns/fields that are relying on this data - which will cause errors to be cascaded to those cells as well. Calculated functions often will not execute with errors in the calculation range. Because of these other fields that appear to need the Daily results, we must use Christian's IFERROR approach to clear the error.

When you use the IFERROR formula, you wrap your entire original formula, parentheses and all, in the IFERROR() function. After inserting the entire formula, you then add a comma followed by whatever you want to happen when an error occurs. Christian's formula was written to force the errored cell to show a blank cell. You can force the cell to almost whatever you like. To make it more visible where you insert the text, I'll show the formula with 'No Match'. To be clear, this formula goes in your Daily column and replaces the original VLOOKUP formula.

=IFERROR(VLOOKUP([Primary Column]@row, {Which country}, 1, false), "No Match")

This says, do the VLOOKUP formula but if any error occurs, insert "No Match". Again, you can insert anything in between the quotes - or if you just use the double quotes with no text inside then a blank cell will be inserted.

You should be able to do your IF statement now in your [Complete/Not Complete] column- whatever you inserted as text is now searchable.

Kelly

https://help.smartsheet.com/function/iferror

Sue08

ok got it

thanks