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.
Best Answer
-
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)
Answers
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!