CODEWITHSUNDEEP
excelexcel-formula
song
song

Reputation: 83

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

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?

Upvotes: 0

Views: 527

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

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.

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53166

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

Upvotes: 1

Related Questions