How to find the first column that matches a criteria in a table

0
votes

I have a table that looks something like this

ID |  July  |  August  |  September  |  
01 |  $ 5   |  $ 6     |   $ 7       |
02 |  $ 6   |  $ 7     |   $ 7       |
03 |  $ 5   |  $ 6     |   $ 5       |
10 |  $ 7   |  $ 5     |   $ 5       |
11 |  $ 4   |  $ 6     |   $ 7       |

Now I want to find the month for when each ID produce $6 or more.

For example, 01 would be August, 02 would be July, 11 would be August.

How do I find the first column that matches the criteria ($6 or more), and return the month as value?

2
excelexcel-formula

2 Answers

1
votes

If you have a version of Excel that supports Dynamic Arrays, you can use FILTER in each row. For row 2 use

=@FILTER($B$1:$D$1,B2:D2>=6,"")

and copy down

1
votes

If one does not have the Dynamic Array formula then use:

=INDEX($B$1:$D$1,MATCH(TRUE,INDEX(B2:D2>=6,),0))

in the first cell and copy down.