Get Index Number Of Selection Made Using Dropdown List

CharlesFrancis

I'm working within the limits of an existing Sheet, and need to get the index number of the value that has been selected from a dropdown list.

For example:

Using the following list as the dropdown for the value in the rows under the Selection column:

Not Started

In Progress

Late

On Deck

Waiting

Selection	Index Level to return
In Progress	2
On Deck	4
On Deck	4

The returned index number above assumes counting the 1st item in the list as 1, but it would be fine if the index starts from 0.

It seems like there should be a way to do this without creating a matrix elsewhere and then doing an (INDEX(MATCH)) against the matrix based on the selected value.

Darren Mullen

@CharlesFrancis Only way to do that without a lookup sheet is to embed the index along with the text then parse if out.

For example, you have your option list:

Not Started [1]

In Progress [2]

.. and so forth. Then when one of those is selected, you parse out the number from between the [] in another column.