Using INDEX/MATCH, but not returning a blank value

Here's what I'm trying to figure out, and hopefully you can assist.

I have a form that adds a new row each time it's completed. Not every field is required though. Using a second sheet, and the INDEX / MATCH functions, I have a sheet that shows the most recent entry for each site. But, when a field on the form (and thus the initial sheet) is blank, it shows a blank on the Master Sheet. I would like for it to show the previous result if the current result is blank. Is this possible?

I am currently using the formula: INDEX({Power_Column}, MATCH(Site@row, {Site_Column}, 0)) whereas "Power_Column" is a Reference Column and "Site" is the column that I am using as a MATCH.

Any suggestions?

Find more posts tagged with

Accepted answers

All comments

Darren Mullen

@Frenla02 Try this...

if(isblank(INDEX({Power_Column}, MATCH(Site@row, {Site_Column}, 0))), INDEX({Power_Column}, MATCH(Site@row, {Site_Column}, 0)-1)).

if(isblank(INDEX({Power_Column}, MATCH(Site@row, {Site_Column}, 0))), INDEX({Power_Column}, MATCH(Site@row, {Site_Column}, 0)+1))

I'm not sure if you need to subtract 1 or add 1 to the index number returned by Match to get the value below the blank one. I also didn't put this in Smartsheet to try it or check that my parentheses are correct :)

Frenla02

Darren,

Thank you for the quick response. I was able to get the formula into Smartsheets (and it accepted it after some minor adjustments), but now the whole column is blank despite there being data on the initial sheet. I tried both the +1 and -1 formulas.

=IF(ISBLANK(INDEX({Power_Column}, MATCH(Site@row, {Site_Column}, 0))), INDEX({Power_Column}, MATCH(Site@row, {Site_Column}, 0) - 1))

Any idea what I'm doing wrong?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.