Very simple if/and formula returning blank cell

Josh Reed

I'm incredibly confused by the results I'm receiving

It's literally just IF(AND(Column1@row = "this", Column2@row = "that"), "say this")

The conditions are met, yet the result is a blank cell. I've used this same function syntax all across this sheet and it works fine. Am I running into formula limits or something? I was attempting to build this into a more complicated function that uses other ifs and even some index and matches but I noticed adding this to the function changed nothing. Then I decided to test just this simple part by itself and it was returning blank cells.

Anyone have a clue what's going on here? I've searched for others having this kind of issue and can't find anything.

Thanks,

Josh

Jeff Reisman

Yep, that is really weird. Try selecting the text inside one of the field values of "France" and Ctrl-C it, then Ctrl-V it over the "France" in your formula.

Jeff Reisman

@Josh Reed

What happens if you just use one criteria in the IF and include a negative condition?

=IF([Engagement Budget Country]@row = "France", "test", "fail")

What are the column types we're dealing with?

Are you sure your text values don't have leading or trailing blank spaces?

Josh Reed

@Jeff Reisman

Thank you for your response, Jeff.

Adding a fail condition does return a value, so that eliminates the possibility of running out of space on the sheet.

There's definitely no inadvertent spaces anywhere.

The two columns are just text/number columns but they are populated by a simple =@row. I did try switching to the original columns and it made no difference. The original two columns that are populating the two I'm using are just simple drop down columns that come in from a form (although the dropdowns are limited to preselected options, not sure why that'd be relevant but..)

Here is the whole formula:

=IF(AND([Expert's Country of Origin]@row = "France", [Engagement Budget Country]@row = "US"), "Melissa Petit", IF(AND([Expert's Country of Origin]@row = "France", [Engagement Budget Country]@row = "France"), "Sandrine Gomes-Lacoste", IF(AND([Expert's Country of Origin]@row = "France", [Engagement Budget Country]@row = "Germany"), "Sandra Brose", IF(AND([Engagement Budget Country]@row <> "US", [Expert's Country of Origin]@row = "United States"), "NAYA US", IF(AND([Engagement Budget Country]@row = "US", [Expert's Country of Origin]@row = "United States"), "Trace NP", INDEX({Country & Gatekeepers NAYA}, MATCH([Expert's Country of Origin]@row, {Country & Gatekeepers Country}, 0)))))))

The first part is what is not working, in bold. What is so strange is that the beginning of the second part is exactly the same and does work...

the weirdest part is: IF(AND([Engagement Budget Country]@row = "US", [Expert's Country of Origin]@row = "United States"), "Trace NP" totally works and returns "Trace NP"

This is leading me to believe that the issue is with the word "France" somehow. Like I need to delete and retype it for all my dropdown menus or something.

Jeff Reisman

Yep, that is really weird. Try selecting the text inside one of the field values of "France" and Ctrl-C it, then Ctrl-V it over the "France" in your formula.

Josh Reed

So odd. I retyped France into one of my drop downs... it then said "10 items do not match anything on the list" for the ten that had France re-selected. Additionally if I copy and paste the new "france" into the cells that said they don't match the list... this breaks the index and match in other columns referencing the word france to another sheet.

I did copy and paste a long list of countries from the secondary sheet being referenced, perhaps that's where the issue lies... but if I just type out France, somehow it can no longer be found on that other sheet and the MATCH returns an error. If I go to the other sheet and copy the word France from there and replace the typed one in the dropdown, the match works again when France is selected.

I looked at the secondary sheet and the column type is just text, no weird spaces.. very very odd lol. Now that you helped me identify the issue it'll just take some troubleshooting to make something work.

Thank you for your time!

Josh

Jeff Reisman

@Josh Reed

That actually makes sense, if the original list came from elsewhere. Programs like Microsoft Word can do some weird things to text, so that sometimes the underlying encoding doesn't match what is elsewhere in Smartsheet. For instance, if you use Word to type out a formula that uses double quotes (ex. =COUNTIF({range}, "Captain America")) and copy that into Smartsheet, it's not going to work. This is because the double quotes used in Word are encoded differently than the double quotes used by Smartsheet.

A best practice I use for copying text values into Smartsheet from elsewhere is to run it through Notepad first. Notepad encodes pasted text as simple ASCII, which Smartsheet recognizes completely.

Josh Reed

So I figured it out. The sheet I was using to index and match the country name was also being filled out by an index and match to another sheet. Somewhere in translation the six keystrokes that = France got turned into glop. I copied the word France from the origin sheet that everything else was built upon and pasted it EVERYWHERE. That fixed it.

ironic that France is always the problem.

Thanks again