Match second occurance of value

[John]

I am trying to match the second time a value occurs in a horizontal range.
This formula sometimes works and other times does not.... the value I am
matching is TRUE
=HLOOKUP(TRUE,$B$6:$K$7,2,TRUE)

 

[Glenn]

I am trying to match the second time a value occurs in a horizontal range.
This formula sometimes works and other times does not.... the value I am
matching is TRUE
=HLOOKUP(TRUE,$B$6:$K$7,2,TRUE)

This array formula (commit with CTRL+SHIFT+ENTER) should work:

=INDEX($B$7:$K$7,,SMALL(IF($B$6:$K$6= TRUE ,
COLUMN($B$6:$K$6)-COLUMN($B$6)+1,""), 2 ))

 

[John]

=INDEX(B6:K7,2,SMALL(IF(B6:K7=TRUE,COLUMN(B6:K7)-COLUMN(B6)+1,COLUMN(B6)+1),2))

as array worked....

 

[John]

actually that doesn't quite work either

=INDEX(B6:K7,2,SMALL(IF(B6:K7=TRUE,COLUMN(B6:K7)-COLUMN(B6)+1,COLUMN(B6)+1),2))

as array worked....

 

[John]

thats what I just came up with thanks!

This array formula (commit with CTRL+SHIFT+ENTER) should work:

=INDEX($B$7:$K$7,,SMALL(IF($B$6:$K$6= TRUE ,
COLUMN($B$6:$K$6)-COLUMN($B$6)+1,""), 2 ))
.

 

[Bernd P]

Hello John,

=HLOOKUP(TRUE,INDEX(B6:K6,MATCH(TRUE,B6:J6,0)+1):K7,2,0)

Normal function, not an array-formula.

Regards,
Bernd

 

[T. Valko]

If there will only be 2 instances of the lookup_value...

=LOOKUP(2,1/(B6:K6=TRUE),B7:K7)