Vlookup first non-blank match

Hi all,

I've spent a few hours trying to find the solution and am about to give up. I've seen similar questions but the question/answers don't make sense for my brain today.

I have 2 sheets:

1- One Sheet with a list of contracts. Some people have more than one contract, with some services included on one contract and other services included on the second contract.

2- Second Sheet with a list of services that took place. On this sheet, I have a column where I check if the service was included in the contract of the person.

Issue: SmartSheet returns the first match, which in some cases is a BLANK because the person in question has two contracts and when SmartSheet checks for that persons name and the type of service, it returns that first match. I need it to return the first NON Blank Match.

See below screen shots and formula currently used. Thanks so much in advance for your help.

Find more posts tagged with

Sheets

Formulas

Accepted answers

Scott Peters

Hello @JDen - Have you considered switching to using an INDEX/COLLECT?

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

This should allow you to look at whether they have a contract (first criteria) AND whether a service is tied to the contract (second criteria)

All comments

Scott Peters

Hello @JDen - Have you considered switching to using an INDEX/COLLECT?

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

This should allow you to look at whether they have a contract (first criteria) AND whether a service is tied to the contract (second criteria)

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.