Vlookup first non-blank match

JDen

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.

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)

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)