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)

John wrote:
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 ))

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

as array worked....

"John" wrote:

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)

actually that doesn't quite work either

"John" wrote:

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

as array worked....

"John" wrote:

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)

thats what I just came up with thanks!

"Glenn" wrote:

John wrote:
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 ))
.

Hello John,

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

Normal function, not an array-formula.

Regards,
Bernd

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

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

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
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)