Using ARRAY.JOIN in an INDEX and MATCH formula

Triedtwice

New Member
Joined
Feb 17, 2014
Messages
28
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,886
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

New Member
Joined
Feb 17, 2014
Messages
28
Thanks for checking it out...easy enough to do a work-around, but I was really hoping it would work.
 

Forum statistics

Threads
1,082,570
Messages
5,366,364
Members
400,886
Latest member
Fchel

Some videos you may like

This Week's Hot Topics

Top