If cell in an array is > value X return value in Row '3' ,Col 'X'

trying to see if i can do this without VBA

I have a variable size matrix starting in cell B3
I want to find that for any value in a row (eg Row4) is greater than a predefined value (in D1)
If so return the cell value in Row 3 directly above this value to column A for each row (ie if F4 > D1 then A4 = F3)
finish off with a conditional format based on the value returned in column A for each row (if A4 = F3 then shade blue)

If two cells in a row are > D1 then return "1" in col A
If No cells are > D1 return "0" in col A

I can easily do do the "1", "0", or value part
I cant figure out how to aquire the cell reference of my target

I havent been able to use MATCH+OFFSET
-as any particular row array isnt in ascending/descending order
-and exact match wont work either

Is a macro the best way and to
-iterate through each row
-create an array of the row contents
- if array contains 1 instance above value D1
- then locate the largest number in array
-apply formating to particular row column A dependent on location identified in array??

And run this macro whenever D1 value changes

Re: If cell in an array is > value X return value in Row '3' ,Col 'X'

alternatively i could find the highest number in a row and IF > than D1 do the same

Again, i dont know how to return the location of a cell using this in formula

Re: If cell in an array is > value X return value in Row '3' ,Col 'X'

Thanks! What i didnt think of was using MAX to find the value i was looking for!

=IF(COUNTIF(C4:Z4,">"&$D$1)=1,OFFSET($B$3,0,MATCH(MAX(C4:Z4),C4:Z4,0)),IF(COUNTIF(C4:Z4,">"&$D$1)>1,"2","0"))

i used this
i cant see any difference except the order in which it is handled and i can read it easier

i then just used a macro to create 26 conditional format rules to create my 26 different colours as it only needs running to create this project. 24 as C to Z is 24 column headers + 2 for two different fail categories

its frustrating knowing all the components to this but just not getting that i specify from the matrix what im trying to find!