Clash Royale CLAN TAG

Excel Formula to compare columns and output a value?

Based on fields containing the Release Name and Change Date, I need to determine in what stage of that release process, the change occurred. In the example below, the change occurred in the January Analysis stage. I need to do this a few hundred times, so I need a formula that will get me there.

You are given a Month and Date.

You will see the month, phase, and date range(Start & Calculation End). The month and date range are used to determine the phase. Therefore for January 10/10/17 falls between January 10/9/17 - 11/5/2017 which would put it in the Analysis phase. I hope this clarifies the issue. Below is a link to an image of how the Stage is determined and spreadsheet is setup.

Phase Determination

Release Date
January 10/10/2017

Release Stage Start Calculation End

January Form Release 8/14/2017 9/10/2017

January Requirements Review 9/11/2017 10/8/2017

January Analysis 10/9/2017 11/5/2017

January Development 11/6/2017 12/10/2017

January Functional Test 12/11/2017 12/17/2017

January QA 12/18/2017 1/7/2018

January Live 1/7/2018 1/7/2018

February Form Release 9/11/2017 10/8/2017

February Requirements Review 10/9/2017 11/5/2017

February Analysis 11/6/2017 12/10/2017

February Development 12/11/2017 1/7/2018

February Functional Test 1/8/2018 1/14/2018

February QA 1/15/2018 2/11/2018

February Live 2/11/2018 2/11/2018

2 Answers
2

You can use a VBA macro to loop through the rows in the table and look for the row that matches the criteria. Below is some VBA that will do it, you can modify this code to use cell references for the inputs, and for the range to search instead of ActiveSheet.UsedRange if you want.

Sub getStage()
Dim release As String
release = "January"
Dim eventDate As Date
eventDate = "2017-10-10"

For Each Row In ActiveSheet.UsedRange.Rows
If (Row.Cells(1, 1) = release And Row.Cells(1, 3) <= eventDate And Row.Cells(1, 4) >= eventDate) Then
Debug.Print (Row.Cells(1, 2))
End If
Next
End Sub


Here's a relatively straightforward way to do this, as I understand the question.

A2:D8

A9:D15

INDIRECT

INDEX

MATCH

MATCH

INDEX

INDIRECT

MATCH

This is the formula in H2:

H2

=INDEX(INDIRECT(F2),MATCH(G2,INDEX(INDIRECT(F2),,3),1),2)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.