Index/Match formula?

Gabriela Ramirez

Hi! I'm trying to create a formula on the Sheet Summary that allows me, based on the Sprint number, to retrieve Start Date and End Date.

I've tried to use =INDEX(([Start Date]:[Start Date]), MATCH([Actual Sprint]#, [Sprint #]:[Sprint #])) but it's giving a No Match error.

Kelly Moore

Hey Gabriela

I just tested the formula in my sheet and it works.

What is the formula that is gathering the Actual Sprint, and how is the Sprint # inserted. Try this

=INDEX([Start Date]:[Start Date], MATCH(VALUE([Actual Sprint]#), [Sprint #]:[Sprint #],0))

Kelly Moore

Hey @Gabriela Ramirez

There were a couple of tweaks to make with your syntax. Try this

=INDEX([Start Date]:[Start Date], MATCH([Actual Sprint]#, [Sprint #]:[Sprint #],0)) 

Does this work for you?

Kelly

Gabriela Ramirez

Hi @Kelly Moore, thanks! It still says No Match :(

Kelly Moore

Hey Gabriela

I just tested the formula in my sheet and it works.

What is the formula that is gathering the Actual Sprint, and how is the Sprint # inserted. Try this

=INDEX([Start Date]:[Start Date], MATCH(VALUE([Actual Sprint]#), [Sprint #]:[Sprint #],0))

Gabriela Ramirez

Thanks a lot @Kelly Moore, it worked!

What does the VALUE formula add? It was the key to the solution as I can see :D

Kelly Moore

Hey Gabriela

The VALUE function forces a number that is behaving as text to revert back to behaving as a number. Honestly, it was a guess on my part- I didn’t know if it would work for this. That’s why I was asking about the formula’s that generated each column entry. I’m glad the VALUE worked.

Kelly