Looking to find the cell returning the first number greater than 1 and return the column header for that cell

smiley359

New Member
Joined
Apr 22, 2022
Messages
2
Office Version
  1. 365
I've got my column headers as dates Column C:N and my rows 2-3 contain the numbers. I'd like to find the cell in row 2 that first reached a number >=1 and return the date in column header.
I'd like to have Column T return the column header for the cell that first meets my criteria.

My formula is =IFERROR(LOOKUP(2,1/(c2:S2>1),C1:S1),"")
but it returns the last column header for the cell that last met the criteria and not the first cell meeting my criteria.
T2 should return 5/1/21 and T3 should return 9/1/21
What am I doing wrong?

1650658230799.png
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
See if the following array formula works for you:
Excel Formula:
=IFERROR(INDEX($C$1:$S$1,MATCH(TRUE,C2:S2>1,0)),"")
 
Upvote 0
Solution
Since you have Microsoft 365, you could also use the new FILTER function.. In your original question you said >=1 (though I note your thread title does not) so I have used that.

22 04 24.xlsm
CDEFGHIJKLMNOPQRST
15/01/216/01/217/01/218/01/219/01/2110/01/2111/01/2112/01/211/01/222/01/223/01/224/01/225/01/226/01/227/01/228/01/229/01/22
22222222333335/01/21
30001281111111111118/01/21
4000000000000 
50505050505056/01/21
Earliest Date
Cell Formulas
RangeFormula
T2:T5T2=INDEX(FILTER(C$1:S$1,C2:S2>=1,""),1)
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,144
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top