Index(Collect with multiple criteria

ross chambers

I'm trying to make a formula in a sheet summary that checks if the "duration" column is not blank and then uses all rows that arent blank as a range to then find the row with the newest "created" date and return a value from a different "IndexKey" column. I'm not sure if i need to use an IF staement to limit the range or not but this is what i have =INDEX(COLLECT(IndexKey@row, Created:Created, MAX(Created:Created), [Duration (weeks)]:[Duration (weeks)], <>""),1) but i'm getting UNPARSEABLE

ross chambers

=VLOOKUP(LARGE(COLLECT(Created:Created, [Duration (weeks)]:[Duration (weeks)], <>""), 1), Created:[Priority Rating], 5, false) this seems to be working i had just forgotten the false part so it was giving me a #no match when i changed values in the duration column

Paul Newcome

Are you able to provide screenshots for context?

ross chambers

I've moved the duration column over to capture it in screenshot

but basically I want to find the newest date when the duration column isn't blank so I now have =LARGE(COLLECT(Created:Created, [Duration (weeks)]:[Duration (weeks)], <>""), 1) and that works but when I add that part to an index or vlookup I get #invalid data. I want to have multiple of this formula that just return a different column instead of Indexkey

Paul Newcome

Are you trying to pull in the INDEX Key that has the largest date? I'm not sure I understand exactly what you are trying to accomplish.

ross chambers

i want the index key for the row most recently created and has a number in the duration column

ross chambers

=VLOOKUP(LARGE(COLLECT(Created:Created, [Duration (weeks)]:[Duration (weeks)], <>""), 1), Created:[Priority Rating], 5, false) this seems to be working i had just forgotten the false part so it was giving me a #no match when i changed values in the duration column