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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 287 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!