Advanced Vlookup

MariuszDworiczak

Hi Guys, I am looking for the formula to return value for a cell which is in the range.

I have one smartsheet like above in the second smartsheet I have cell with date and for example if I have there 08/02/24 I want formula to give back Q3 FY25. Any ideas how can achieve this?

Nick Korna

Hi @MariuszDworiczak,

You can do this with an INDEX COLLECT:

=INDEX(COLLECT({Quarter}, {Start Date}, @cell <= Date@row, {End Date}, @cell >= Date@row), 1)

Sample output:

The references in { } brackets are cross sheet references, if you're unsure on how to create these, then the below should help:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Hope this helps, but if you've any problems/questions then just ask!

Nick Korna

Only the quarter.

MariuszDworiczak

@Nick Korna Now it is working =INDEX(COLLECT({Quarter}, {Start Date}, @cell <= [Final Migration Date]@row, {End Date}, @cell >= [Final Migration Date]@row), 1). I don't know why it didn't work first time but BIG THANK YOU!

Nick Korna

No problem, glad it is all resolved now!

Nick Korna

Hi @MariuszDworiczak,

You can do this with an INDEX COLLECT:

=INDEX(COLLECT({Quarter}, {Start Date}, @cell <= Date@row, {End Date}, @cell >= Date@row), 1)

Sample output:

The references in { } brackets are cross sheet references, if you're unsure on how to create these, then the below should help:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Hope this helps, but if you've any problems/questions then just ask!

MariuszDworiczak

Hi @Nick Korna Thank you. I did as you wrote. I name the reference accordingly =INDEX(COLLECT({Quarter}, {Start {Start Date}}, @cell <= [Final Migration Date]@row, {{End Date}}, @cell >=[Final Migration Date]@row), 1). But I have #unparaseable

as temporary solution I used:

=IF([Final Migration Date]@row < DATE(2024, 5, 1), "FY25 Q1", IF([Final Migration Date]@row < DATE(2024, 8, 1), "FY25 Q2", "FY25 Q3"))

but would prefer using external file with dates

MariuszDworiczak

@Nick Korna a question your reference {Quarter} is for column Quarter only or it contains all three columns ?

Nick Korna

Only the quarter.

MariuszDworiczak

@Nick Korna Now it is working =INDEX(COLLECT({Quarter}, {Start Date}, @cell <= [Final Migration Date]@row, {End Date}, @cell >= [Final Migration Date]@row), 1). I don't know why it didn't work first time but BIG THANK YOU!

Nick Korna

No problem, glad it is all resolved now!