Using ARRAY.JOIN in an INDEX and MATCH formula

[Triedtwice]

I'm trying to write an "index & Match" formula that uses the "Morefunc" function, "ARRAY.JOIN" to join nonadjacent ranges, such as:

=INDEX((ARRAY.JOIN(C3:C30,G3:G30)),MATCH(N5,ARRAY.JOIN(B3:B30,F3:F30),0))

This formula results in the #VALUE error. Any suggestions? is it not possible to use ARRAY.JOIN in an index/match formula?

thanks for any help!!!

 

[Scott Huish]

In my testing, it appears to have a problem with INDEX if the values if any of the cells are text and not numbers.

 

[Triedtwice]

Thanks for checking it out...easy enough to do a work-around, but I was really hoping it would work.