VLOOKUP with Multiple Criteria or should use INDEX or COLLECT?
Hi!
I'm trying to do a multiple criteria look up and I'm not sure I'm going about it with the correct formula.
I have a Project ID referenced on a project profile, then the formula needs to find that project and display the data, easy with a vlookup. Here's where it gets fun, I need it to look at the next unique time entry (zap ID) and then display that data.
Here's a video: https://danielle-newburypartners.tinytake.com/msc/NjYwMzMwM18xOTEzNDM4Nw
What formula do I use to look up the one ID match it to the next, then look up a range if ID's and then display just that data going forward?
Thanks!
Answers
-
Hi @Danielle Pineiro,
I try to understand your issue via the video and suggest solution as below:
In "Time Entry" sheet, you create a new "RowID" column and key in value 1,2,3,4,5, ..., 50 for each row
In Zap ID column, instead of using VLOOKUP, you can use INDEX and COLLECT function to collect all the Zap ID in "Time Entry by Zap" sheet that match the criteria of Project ID : = INDEX(COLLECT([Zap ID]:[Zap ID], [Assignable ID]:[Assignable ID], Project ID), RowID@row) (need using reference another sheet feature)
As a result, you will get a unique ZapID list
From here, you can continue to pull data for Entry date, User ID,... by using unique Zap ID value of each row
Hope that helps.
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!