HLOOKUP for first value not blank?

I've tackled this a few ways already but everything comes back with errors. Here's the latest -

=INDEX([T1 BI Blend Options]1:[T1 Data Recon Options]1, MATCH(false, ISBLANK([T1 BI Blend Options]1:[T1 Data Recon Options]1), 0))

Does 'ISBLANK' come back with a value different than false? I tried using the ISBLANK formula standalone but it seems to require being nested in another formula.

If I need to use NOT(ISBLANK how do I tell match that's what I want to pull?

Background: Of the subsequent 15 columns, I want to pull the value that is populated in that row. Below is an example of what I did in excel; I'm exploring if I can replicate in SS.

Thanks in advance!

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

Hi @maggie82

'ISBLANK' indeed comes back with values like true and false.

The MATCH function can not evaluate each value of a range with @cell.😅

So, instead, you can use the COLLECT function to get a range with cell values that are not ISBLANK.

COLLECT([T1 BI Blend Options]1:[T1 Data Recon Options]1, [T1 BI Blend Options]1:[T1 Data Recon Options]1, NOT(ISBLANK(@cell)))

Then, use this range in the INDEX function.

=INDEX(COLLECT([T1 BI Blend Options]@row:[T1 Data Recon Options]@row, [T1 BI Blend Options]@row:[T1 Data Recon Options]@row, NOT(ISBLANK(@cell))), 1)

If there is more than one column with an option value, the second one, for example, would be;

=IFERROR(INDEX(COLLECT([T1 BI Blend Options]@row:[T1 Data Recon Options]@row, [T1 BI Blend Options]@row:[T1 Data Recon Options]@row, NOT(ISBLANK(@cell))), 2), "")

If you are sure that there is only one option value, using the JOIN function is an easier way.

=JOIN([T1 BI Blend Options]@row:[T1 Data Recon Options]@row)

Please check that the function retrieves a non-blank value by changing the cell value in the option column in the demo sheet available from the link below.

https://app.smartsheet.com/b/publish?EQBCT=58611ac8a7f847ca9a7ac8842dc6d40c

All comments

Carson Penticuff

Let me make sure that I correctly understand what you are attempting to do. Each row of your sheet has only one cell within the 15 column range you specified that contains data, and you would like to display that data in a specific column?

Their really is no equivalent to HLOOKUP in Smartsheet. If I understand your case correctly, the easiest way is likely to use a JOIN(). Since only one cell contains data, you will not "really" be joining anything, but it will work.

=JOIN([T1 BI Blend Options]@row:[T1 Data Recon Options]@row)

You could also use COLLECT, but will only be collecting one value:

=COLLECT([T1 BI Blend Options]@row:[T1 Data Recon Options]@row, [T1 BI Blend Options]@row:[T1 Data Recon Options]@row, NOT(ISBLANK(@cell)))

jmyzk_cloudsmart_jp

Hi @maggie82

'ISBLANK' indeed comes back with values like true and false.

The MATCH function can not evaluate each value of a range with @cell.😅

So, instead, you can use the COLLECT function to get a range with cell values that are not ISBLANK.

COLLECT([T1 BI Blend Options]1:[T1 Data Recon Options]1, [T1 BI Blend Options]1:[T1 Data Recon Options]1, NOT(ISBLANK(@cell)))

Then, use this range in the INDEX function.

=INDEX(COLLECT([T1 BI Blend Options]@row:[T1 Data Recon Options]@row, [T1 BI Blend Options]@row:[T1 Data Recon Options]@row, NOT(ISBLANK(@cell))), 1)

If there is more than one column with an option value, the second one, for example, would be;

=IFERROR(INDEX(COLLECT([T1 BI Blend Options]@row:[T1 Data Recon Options]@row, [T1 BI Blend Options]@row:[T1 Data Recon Options]@row, NOT(ISBLANK(@cell))), 2), "")

If you are sure that there is only one option value, using the JOIN function is an easier way.

=JOIN([T1 BI Blend Options]@row:[T1 Data Recon Options]@row)

Please check that the function retrieves a non-blank value by changing the cell value in the option column in the demo sheet available from the link below.

https://app.smartsheet.com/b/publish?EQBCT=58611ac8a7f847ca9a7ac8842dc6d40c

maggie82

Thank you both!

@jmyzk_cloudsmart_jp the Index Collect formula worked flawlessly. I'm so jazzed right now :)

jmyzk_cloudsmart_jp

@maggie82

Happy to help.😁

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.