USING AN ARRAY OUTCOME AS REFERENCE

[Max Alejandro Molina]

Hi Excel gurus,

The following array formula provides the first largest number,having said that I wanna know how to detect if the 5 cells right next to the outcome cell are filled with any value, and if not I want excel to returned the word "FALSE", how can this be achieved?

=SMALL(INDEX($O$4:$S$21,MATCH(LARGE(IF(($J$4:$J$21=B4)*(INDEX($L$4:$N$21,,COINCIDIR(A4,$L$3:$N$3,0))>0),$K$4:$K$21),1),$K$4:$K$21,0),))

Thank you!

 

[Hui]

Max

can you please post a sample file with an example of the required outcome

 

[Max Alejandro Molina]

Here it is. Please see comment in the cell.

Max

can you please post a sample file with an example of the required outcome

 

[Faseeh]

Hi Max,

With 5 cell to the right you mean F4:H4?

 

[Hui]

I assumed it was the to the right of the Looked up position of the date in D4 (or the example in F15) in Column O

I think this does what you want :
=COUNTIF(OFFSET(O3,MIN(IF(O4:O21=F15,ROW(O4:O21)-ROW(O3)+1)),1,1,4),">0")=4 Ctrl+Shift+Enter

See attached file:

 

[Max Alejandro Molina]

I assumed it was the to the right of the Looked up position of the date in D4 (or the example in F15) in Column O

I think this does what you want :
=COUNTIF(OFFSET(O3,MIN(IF(O4:O21=F15,ROW(O4:O21)-ROW(O3)+1)),1,1,4),">0")=4 Ctrl+Shift+Enter

See attached file:

Thanks Hui!