VLookup or not

Im working on a scrum dashboard and want to show the actual sprint name.

The Sprint Name (e.g. Sprint 01, Sprint 02...) is in the primary column at Level 2 (task at Level 3 and Release at Level 1). I have a column with the status ("Do", "Doing", "Done") and only one Sprint can be in the "Doing" status.

On my metrics sheet I can to calculate a cell which give me the name of the sprint which is in Diong and later on the dashboard I will use this cell to show the text "Sprint 01".

Any thoughts?

regards

Dietmar

Find more posts tagged with

Formulas

Comments

Paul Newcome

I suggest an INDEX/MATCH. It works provides the same results as a VLOOKUP but is much more flexible. Try something along these lines...

=INDEX({Main Sheet Primary Column}, MATCH("Doing", {Main Sheet Status Column}, 0))

Dietmar Schmitz

Hi Paul,

that looks good. The only thing i need to consider that I only want the results on level 2 (Sprint 01) . In the moment I get the result of Level 1 (Release 01) which is the parent of Sprint 01.

Can I add a second Match()?

regards

Dietmar

Paul Newcome

Are you able to post a screenshot?

Sign in to join the conversation!

Quick Links

Trending discussions

Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…