Horizontal Dates with Index Match

procurementus

Having trouble with an Index Match function. I have the Match portion working, but not the Index.

• Goal is to display the name of the current stage.
• Yes the three Stage columns and "Index(Match)" column are set as Dates.
• I have the Stages backwards since the Match function reads left to right and some Stages might be completed same day, as shown in example.

• Match Column (working) =MATCH(MAX([Stage C]@row:[Stage A]@row), [Stage C]@row:[Stage A]@row, 0)
• Index(Match) Column (error) =INDEX([Stage C]@row:[Stage A]@row, MATCH(MAX([Stage C]@row:[Stage A]@row), [Stage C]@row:[Stage A]@row, 0))
• Stage Name Column (General Idea) =IF(MAX([Stage C]@row:[Stage A]@row) = INDEX(MATCH()),
  IF(INDEX(MATCH()) = [Stage C]@row, "C",
  IF(INDEX(MATCH()) = [Stage B]@row, "B",
  IF(INDEX(MATCH()) = [Stage A]@row, "A", "Error"))), "")

Jgorsich

Your index() function is programmed to index along the rows of a set of data rather than columns.

INDEX Function | Smartsheet Learning Center

Try:

=INDEX([Stage C]@row:[Stage A]@row, 1, MATCH(MAX([Stage C]@row:[Stage A]@row), [Stage C]@row:[Stage A]@row, 0))

Paul Newcome

A basic nested IF should do this for you.

=IF([Stage C]@row <> "", "C", IF([Stage B]@row <> "", "B", "A"))

procurementus

@Paul Newcome
Thanks for the suggestion. I simplified this example for clarity. In my actual project, there are several more stages, and projects can be rejected and restarted. However, the date isn't erased; it's just copied over. That's why I thought the MAX function would be necessary.

Jgorsich

Your index() function is programmed to index along the rows of a set of data rather than columns.

INDEX Function | Smartsheet Learning Center

Try:

=INDEX([Stage C]@row:[Stage A]@row, 1, MATCH(MAX([Stage C]@row:[Stage A]@row), [Stage C]@row:[Stage A]@row, 0))

procurementus

@Jgorsich

Worked, thank you so much! I figured it was something simple I was overlooking.