Advanced Vlookup
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?
Best Answers
-
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:
Hope this helps, but if you've any problems/questions then just ask!
-
Only the quarter.
-
@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!
-
No problem, glad it is all resolved now!
Answers
-
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:
Hope this helps, but if you've any problems/questions then just ask!
-
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
-
@Nick Korna a question your reference {Quarter} is for column Quarter only or it contains all three columns ?
-
Only the quarter.
-
@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!
-
No problem, glad it is all resolved now!
Help Article Resources
Categories
Check out the Formula Handbook template!
