Operation on a concatenated array

[MRDecarte]

Dear all,

I would like to know if there is a way to use functions on concatenated arrays?
I have 2 ranges of cells in the Name Manager as follows:

Array 1 =Sheet1!$A$1:$A$5,Sheet1!$A$7:$A$10,Sheet1!$A$12:$A$16
Array 2 =Sheet1!$B$1:$B$5,Sheet1!$B$7:$B$10,Sheet1!$B$12:$B$16

When I try to display the array argument in the Index/Match formula it returns Value# error message.
If it does not work for Index/Match function it seems to be working for Large or Small functions. I v tried the Ctrl+Shift+Enter but it s not working.
I v tried to use the nomenclature for array {....} and directly the arrays in the formula but it does not work.

Any idea or help would be much appreciated

Thank you

 

[XOR LX]

Hi,

It all depends precisely what you are wanting to do with those arrays. Some functions implicitly accept range unions, others do not, though there may be workarounds for the latter.

Regards

 

[MRDecarte]

Hi Xor LX

Thank you for your answer.
I understand now why it worked with Large function but not Index/Match. Do you have a workaround to lookup values on discontinued range of data in a same column?

Thank you
Best

 

[MRDecarte]

Hi Xor LX

Thank you for your answer.
I understand now why it worked with Large function but not Index/Match. Do you have a workaround to lookup values on joined arrays of data ?

Thank you
Best

Hi,

It all depends precisely what you are wanting to do with those arrays. Some functions implicitly accept range unions, others do not, though there may be workarounds for the latter.

Regards

 

[XOR LX]

What version of Excel is this for?
Are the values in Array 1 and Array 2 text? Numbers? Or a mixture of both?

Regards

 

[MRDecarte]

Hi XOR LX

I have Excel 2016 at home and 2013 at work.
Basically I set up a ranking table on descending order (largest to the lowest) and I use for this purpose the functions Large and Index/Match functions on a Pivot table as data set. I have used the Name manager to refer the joined data ranges. As I said it works on the large function but no the index/match.
Yes the array of the lookup values are numbers and percentages with duplicates but the array of the Index returns text from the pivot.
Thank you and do not hesitate if further information is needed

Thank you

Best

What version of Excel is this for?
Are the values in Array 1 and Array 2 text? Numbers? Or a mixture of both?

Regards

 

[XOR LX]

So do you need it to work on 2013, or will a 2016 solution be ok?

Also, do you have an Office 365 subscription with your Excel 2016?

Regards

 

[XOR LX]

In any case, this array formula** should work for Excel 2013:

=INDEX(INDEX(Array_2,,,MATCH(1,COUNTIF(INDIRECT(FILTERXML("<a><b>"&SUBSTITUTE(CELL("address",Array_1),",","</b><b>")&"</b></a>","//b")),G1),0)),MATCH(G1,INDEX(Array_1,,,MATCH(1,COUNTIF(INDIRECT(FILTERXML("<a><b>"&SUBSTITUTE(CELL("address",Array_1),",","</b><b>")&"</b></a>","//b")),G1),0)),0))

where G1 contains the value to be searched within Array_1.

Regards