INDEX and MATCH #invalid value error

agp

Hi! I'm trying to apply a formula we got to work in Excel to our Smartsheet.

The goal is to look at a range (Column "1" through Column "Column64") in a single row and find the non-blank cell farthest to the right and return the value from the same column in row 2. (Row 2 was created to replicate the name of the column since it's the column name we really want returned. If there's another way to do this, I'd love to hear that as well.)

We are attempting to use the following formula but it returns an #invalid value error.

=INDEX($[1]$2:$[Column64]$2, MATCH("*", $[1]222:$[Column64]222, -1))

When all the cells are blank, we do get #NA RESULT, so it seems to be partially working.

Thanks!

 

Shaine Greenwood

Hi!

I'm not exactly sure where the error is, but #NA RESULT means that the MATCH function isn't finding anything, which would make since if it's searching across blank rows.

#INVALID VALUE means that the number you're entering in a formula is outside of an expected range of numbers.

Check out the Formula Error Messages article for more information anytime you run into an error message.

I'm not sure if what you're asking can be done in Smartsheet. I'm going to see if I can come up with a formula—but I can't make any promises.

J. Craig Williams

agp,

I'm also slightly confused by your formula example. Where is the 222 row?

You can't search for a blank in Smartsheet. The Norn Fates know I've tried.

You were looking for "*", which implies you are used to RegEx. That won't work either.

However, it is a clue to what will.

I created another row that checks for a blank in the row of concern. If it finds one, I return a *, like so. This formula would be in row 4, column [1].

=IF(ISBLANK([1]3), "*")

and so forth.

The INDEX( ... MATCH()) pair then becomes

=INDEX([1]2:[Column64]2, 1, MATCH("*", [1]4:[Column64]4))

No need to absolute references. 

Hope that helps.

Craig

 

agp

Thanks for the feedback.

I've been using an Excel workaround but trying again to get this to work in Smartsheet.

Rows are Sheets and Columns are Issuances. Each time a Sheet is issued an X is placed in the corresponding Sheet row/Issuance column. On each row I need to identify the last time a sheet was issued - so the last place an X occurs in the row.

 

The following formula (translated for SS column titles) works in Excel, but when I use it in SS it pulls the FIRST place the X occurs, not the last.

=INDEX([1]$2:[Column64]$2, 1, MATCH("x", [Column64]11:[1]11, -1))

In this case, row 2 contains the Issuance name (data I want returned) and row 11 is the row of the Sheet in question.

 

I tried using the sample formula above: 

=INDEX([1]$2:[Column64]$2, 1, MATCH("*", [1]12:[Column64]12))

but that returns the column *before* the FIRST occurrence.

 

Is it possible to do this in Smartsheet?

Thanks!