Welcome!

IF statement referencing another sheet

I'm new to Smartsheet and I'm trying to use an IF statement to pull data from another sheet based on "true" criteria.

I want to look up a reference in a sheet based on an application name, and if it is true, I want to return the site # based (on the same reference sheet, different range), otherwise it returns "None". I have done this successfully in Excel many times, but I cannot get it to work in Smartsheet (#Invalid Operation).

I've tried it using 2 separate reference ranges (as shown below--I'm using specific columns within the reference sheet here), as well as a broader range using column #s).

What am I doing wrong?

SmartSheet:

=IF({Reference Range 1-SystemType} = "AppName", {Reference Range 2-SiteName},"NONE")

Excel:

=IF(Table_1[SystemType]="AppName",Table_1[SiteName],"NONE")

Find more posts tagged with

Comments

J. Craig Williams

You don't use IF for this situation in Smartsheet.

Instead use INDEX and MATCH

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

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

The VLOOKUP function can also be used, but there are many disadvantages to it over INDEX(..., MATCH())

Your formula will likely look something like this:

=IFERROR(INDEX({Reference Range 2-SiteName},MATCH("AppName", {Reference Range 1-SystemType},0)),"NONE")

general form:

=IFERROR(INDEX({range with value to find}, MATCH(search-term, {range to search}, 0)), return-when-search-term-not-found)

Craig

Sldollman

Thanks, Craig. That got me much closer. However, It's returning the same value for every row when I copy the formula to the next row (it's returning the same value of the first site in the list). What do I do to get it to move down the list?

J. Craig Williams

Is your search-term changing?

You will want to change the search-term ("AppName") with a reference to a cell containing that text and then the next search-term would be in the cell below it.

"AppName" was in ColA of the same row, you could use [ColA]@row. When you copy it to the next row, it will look for the value in ColA but on the new row.

Craig

Sldollman

Still no luck.

I've tried changing my reference to a range in the reference sheet, and individual columns. I can get it to return the correct value in the first row, but then it returns the same value in subsequent rows (so to answer your question Craig, no, my search term is not changing).

I'm viewing the reference as the equivalent to an Excel table where you don't need to specify rows/cells, but that may be the problem.

Here's what the formula that works for the first row looks like: (note that these references (SiteName and SystemType) are in the same sheet, just different columns -- i've also tried referencing a range within the sheet, but that doesn't work at all)

=IFERROR(INDEX({SiteName-Ref1},MATCH("AppName",[SystemType-Ref2},0)),"NONE")

You mentioned to add the column name and @row, but there is only one column in each reference and if I try to add [ColA] after the reference, it returns UNPARSEABLE.

The smartsheet help files on this are so vague... I'm just not sure where I need to add information to make this work.

Any help is greatly appreciated.

My next stop is Smartsheet support...

Thank you!

Sign in to join the conversation!

Quick Links

Trending discussions

Getting the Triggered by at least 5 other workflows but nothing is daisy chained
Hello, I'm seeing this error for the first time as I expand some capability in an existing workflow. Here's how this section with the error works. Scenario: a user puts in a request on the form with a check box indicating they will later upload the required file attachment. Automation sends a request for Approval with…
Re assign task midway thru
I have a task that started in Sept and ends in Feb. I need to assign it to someone else, but do not want to lose the history of the first employee. How do I do this?
Suddenly receiving multiple emails titled "Your Smartsheet Order has been Activated"
Starting a couple of days ago, I started getting 2 or more emails a day about my account being activated. I have a monthly subscription but have never received this type of email from Smartsheet. The email looks legitimate - the sender is "reply_to@Smartsheet .com" - except for maybe the greeting, which is "Dear Customer".…