Find the first column in a row that is greater than a specific value

[Ben Thiele]

Hello,

As I understand it, the match function with the "greater than" option selected only works for sorted data. What formula will give me the column location of the first cell in an unsorted row that is above an arbitrary level, like 100,000?

Thank you,
Ben

 

[NARAYANK991]

Hi Ben ,

Try this :

=MIN(IF(Unsorted_Row_of_Data>=Threshold,COLUMN(Unsorted_Row_of_Data)))

entered as an array formula , using CTRL SHIFT ENTER.

Unsorted_Row_of_Data can be a range such as F17:O17

Threshold is a cell containing your cutoff value , say 100,000.

The column number returned will be the absolute column number , such as 6 for column F , 10 for column J.

Narayan

 

[Somendra Misra]

Hi Ben,

Alternate solution as shown in below pic, using INDEX-MATCH combination. Note the formula shown in formula bar is an array formula so must be entered with Ctrl+Shift+Enter.



Formula is in D11.

Regards,

 

[Ben Thiele]

I've just gotten both methods to work. Thanks so much, Narayan!