VLOOKUP with Multiple Criteria or should use INDEX or COLLECT?

Danielle Pineiro

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!

Gia Thinh

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.