Contiguous "Range" from Non Contiguous Ranges

before reading note I have not tried to use 100% accurate terminology - it's in donkey speak - ie written in a way I *think* makes sense and won't cause confusion...

c/o daddylonglegs we know that we can use CHOOSE to create contiguous "ranges" from non-contiguous ranges where those ranges share the same dimensions, eg:

Please Login or Register  to view this content.

the above in essence being a right to left VLOOKUP

As illustrated by daddylonglegs here in post # 5 we know that we can also use CHOOSE to construct contiguous "ranges" from non-contiguous ranges with differing dimensions by processing each cell individually, eg:

Please Login or Register  to view this content.

so here we are conducting a MATCH against 2 non-contiguous ranges of different dimensions (B1:B2, B5:B7) but as a single contiguous "range" of 5 cells.

So here's my question... can we create a single contiguous "range" from multiple non-contiguous ranges of differing dimensions but on an area by area basis rather than cell by cell ?

Please read post in full before posting suggestions re: restrictions etc...

Consider:

Please Login or Register  to view this content.

The aim is to replicate the earlier MATCH but rather than using CHOOSE on a cell by cell basis we want to use the Areas (B1:B4, B6:B7, B9:B10) ... in pseudo terms:

Please Login or Register  to view this content.

A CHOOSE construct here won't work:

Please Login or Register  to view this content.

as we will end up with #N/As given different dimensions...


For sake of clarity - given the sample values the answer we expect is 8 given "apple" is the 8th item to appear within the 8 valid items.


To reiterate - the aim is to create a single contiguous array of 8 items for use in a single MATCH function with following restrictions:

a) we are not looking to operate 3 different tests (1 per area)

b) we are not looking to create an array of 10 items in which the 2 values to be discounted are erased/modified (B5, B8)

c) UDF / VBA is not permitted

d) 3rd Party Function are not permitted (eg ARRAY.JOIN morefunc)

edit:
e) solution should be scalable (within reason) though not nec. truly dynamic

I don't think it's possible given the fact the various arrays are of different dimensions but I'm perhaps not thinking outside the box enough.
I would love to see a workaround if one exists however inefficient / ugly / long winded it might be.

I see you have been around ElmerS too much.

I think the crux lies here:

Please Login or Register  to view this content.

What you call 'the construction of a continuous range' isn't a continuous range at all. It is a set of separate (distinct) cells.
Even if the areas have the same dimensions, as far as I can test it, it wouldn't even be possible. Not because of the dimensions, but because it has nothing to do with 'constructing an array'.

Given the other restraints, I can only suggest to perform this with either testing each area separately (what can be done in 1 formula), or by using the choose() function (with the restriction of 30 cells).

Originally Posted by snb

Given the other restraints, I can only suggest to perform this with either testing each area separately (what can be done in 1 formula), or by using the choose() function (with the restriction of 30 cells).

@snb, I don't disagree with any of your points though I believe "testing each area separately" would still fall foul of restriction a) [ie separate tests - even if all conducted within one formula]
If the above is an incorrect interpretation of what you had in mind please post an example.

@all, I guess I should outline that I've (long) been curious as to the possible uses for the optional Areas parameter within INDEX.
In this instance for ex. whether or not we could utilise INDEX w/Areas in conjunction with a Named Range of non-contiguous ranges, however, like snb I can't see any way around separate tests (one per area).

I've seen some clever uses in the past of ERROR.TYPE (to test if a cell resides within a given named range) but as far as I can see that approach too would (per alternatives) generate an "output" for each of the ten cells which was not the intention.

Although it doesn't comply with your constraint #1, this was what I meant with 'testing each area separately':

=IF(COUNTIF(B1:B4,A1)=0,ROWS(B1:B4),MATCH(A1,B1:B4,0))+IF(COUNTIF(B6:B7,A1)=0,ROWS(B6:B7),MATCH(A1,B6:B7,0))+IF(COUNTIF(B9:B10,A1)=0,0,MATCH(A1,B9:B10,0))

I also tried this one; maybe it stimulates your brainwaves:

=SUMPRODUCT((B1:B10=A1)*(ROW(B1:B10)<>5) * (ROW(B1:B10)<>8)*ROW(B1:B10))-CHOOSE(SUMPRODUCT((B1:B10=A1)*(ROW(B1:B10)<>5) * (ROW(B1:B10)<>8)*ROW(B1:B10)),0,0,0,0,0,1,1,1,2,2)

... whether or not we could utilise INDEX w/Areas in conjunction with a Named Range of non-contiguous ranges,

INDEX is stubbornly resistant to the use of arrays (even literal arrays) in its arguments, for no logical reason I can fathom.

I don't see any problem using index on a non-contiguous range.
In DO's example, to get the first item in the 3rd area:

=INDEX((B1:B4;B6:B7;B9:B10);1;;3)

resulting in 'fish'. (or am I overlooking something ?)
But using the area argument doesn't solve the problem.

@snb's formula above is an example of what I was referring to in post # 5 re: optional Areas parameter within Index

However, the problem as shg points out is that we can't use a construct like:

Please Login or Register  to view this content.

It would be great if we could use constructs like the above esp. as we could create the Area iteration dynamically (1 to n) - unfortunately we can not use this construct.

I think at this stage we've all reached the same conclusion that given the (extensive) constraints I set out it's not possible

Thanks everyone for their input.