Match second occurance of value

  • Thread starter Thread starter John
  • Start date Start date
J

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)
 
John said:
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)
Click to expand...


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....
 
thats what I just came up with thanks!

Glenn said:
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 ))
.
Click to expand...
 
Hello John,

=HLOOKUP(TRUE,INDEX(B6:K6,MATCH(TRUE,B6:J6,0)+1):K7,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)
 
Back
Top