IF statement referencing another sheet

Sldollman

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")

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!