Find Discontinued Number

[Leo Heuser]

No, it should return {2,65537;3,65538;4,65539;...;65536,131071}.
Thanks!


If you misunderstand how my expression works, don't be surprised that you
fail to anticipate what it returns.

I didn't misunderstand. I *asked* if I had understood it properly, expecting
that it would return the missing numbers (which was, what the OP wanted),
and you gave me a straight answer.
Why add these poisoneous remarks?

...

Um, the normal way, but it's not supposed to be directly entered in a
worksheet. It's meant to be fed to SMALL. Note: you can select
R1C1:R65535C50 and enter the array formula

=ROW(INDIRECT("1:65535"))+65535*(COLUMN(INDIRECT("A:AX"))-1)

and it'll populate the range with sequential integers from 1 to 50*65535.
Can you not do this in Excel on your PC?


OK, you're right. COUNTIF doesn't work with arrays. If the array in question
(smallarray) is sorted, however,

LOOKUP(bigarray,smallarray)=bigarray

is more efficient than ISNA(MATCH(bigarray,smallarray,0)).


Well, it's an assumption, but the OP's array did appear sorted, and some
data processing experience would show that finding gaps in sequences is much
easier when the list is sorted. So for this sort of application, assuming
the list is sorted is a hint that the list should be sorted.

As for VLOOKUP and HLOOKUP, given the following table in A1:B20

A 3
D 5
E 53
G 77
G 87
H 15
L 12
L 12
L 62
M 36
O 58
O 75
P 83
R 31
S 41
T 57
U 4
V 35
Y 64
Z 15

the formula

=VLOOKUP({"A","E","I","O","U"},A1:B20,2)

if entered into a 5 column by 1 row range returns

3 53 15 75 4

And similarly for HLOOKUP. It's not that these functions can't take array
1st arguments, the problem is that they don't return arrays when fed such
1st arguments. They return something akin to what INDEX returns when fed
array 2nd or 3rd arguments.

That's what I meant.
I was talking about single-cell not multi-cells formulae.
LOOKUP can return an array, HLOOKUP and VLOOKUP can't.

LeoH

 

[Harlan Grove]

...
...

I was talking about single-cell not multi-cells formulae.
LOOKUP can return an array, HLOOKUP and VLOOKUP can't.

You're being overly broad in your language. [V|H]LOOKUP *can* return arrays when
their *3rd* argument is an array, but not when their *1st* argument is an array.
They may be the oddest functions in all of Excel. That INDEX called with array
2nd or 3rd arguments can't return arrays to single cells is at least
understandable because it normally returns range references. That's not the case
for [V|H]LOOKUP.

 

[Leo Heuser]

...
..

I was talking about single-cell not multi-cells formulae.
LOOKUP can return an array, HLOOKUP and VLOOKUP can't.

You're being overly broad in your language. [V|H]LOOKUP *can* return arrays when
their *3rd* argument is an array, but not when their *1st* argument is an

array.

Overly broad!?
Maybe you're being overly narrow (if such an expression exists in English)
Obviously it's necessary to quote from my earlier writings:

Quote:

.................................it would be nice, if VLOOKUP and
HLOOKUP could take a vector as their first argument.

End quote

Combining the uppermost paragraphs with the quote should make it obvious,
that what I'm saying is, that in a single-cell formula HLOOKUP and VLOOKUP
can't return arrays, if the first argument is a vector.

Right?

LeoH

 

[Harlan Grove]

...
...

that what I'm saying is, that in a single-cell formula HLOOKUP and VLOOKUP
can't return arrays, if the first argument is a vector.

Right?

Wrong!

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3})

returns {1,10}. You are failing to be precise.

[V|H]LOOKUP can return an array if the 3rd argument is an array NO MATTER WHAT
THE 1ST ARGUMENT MAY BE. On the other hand, [V|H]LOOKUP won't return arrays when
the 3rd argument is a scalar, but they will return bizarre, undocumented,
implicitly indexed collection objects if the 1st argument is an array.

 

[Leo Heuser]

...
..

Wrong!

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3})

returns {1,10}. You are failing to be precise.

And I feel, that you are twisting my words by giving an example, that
you wouldn't meet in the real world.

You can do:
=VLOOKUP({"a","b","c","d","e"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3})
and still get {1,10}, because the function only uses the first element in
the array,
so the function is the same as
=VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}), and nobody
in their right mind would insert a vector, when it can't be used by the
function.

LeoH

 

[Harlan Grove]

...
...

You can do:
=VLOOKUP({"a","b","c","d","e"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3})
and still get {1,10}, because the function only uses the first element in
the array,
so the function is the same as
=VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}), and nobody
in their right mind would insert a vector, when it can't be used by the
function.

The following formula entered in a single cell returns 11.

=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}))

Select E1:F2 and array-enter the formula

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3})

and you get

1 3
1 3

For that matter, select E1:E2 or E1:F1 and array-enter

=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}))

and you get 4!

Ain't VLOOKUP fun?!

 

[Leo Heuser]

...
..

The following formula entered in a single cell returns 11.

=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}))

So do

=SUMPRODUCT(VLOOKUP({"a","b","c","d"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},
{2;3}))

and

=SUMPRODUCT(VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}))

What's your point?

Select E1:F2 and array-enter the formula

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3})

and you get

1 3
1 3

For that matter, select E1:E2 or E1:F1 and array-enter

=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}))

and you get 4!

Ain't VLOOKUP fun?!

I seem to catch a glimpse of a pattern here:

For the [V][H]LOOKUP functions:
The first and the third argument can both be vectors, but not at the
same time, i.e. if they are at the same time, one of them may be
replaced by a scalar equal to the first element of the vector. The
following rules seem to apply (array-enter where appropiate):

For single-cell entries:
First argument is a scalar, third argument may be a vector or a scalar.

For multi-cell entries:
First argument may be a vector or a scalar, third argument is a scalar.

It may not be precise enough for you, but I believe you get my drift.

I'm not stating, that I have the truth, the whole truth and nothing but the
truth, but as a starter, I believe it's OK.


LeoH

 

[Don Guillett]

Don't you just love a good p _ _ _ _ y fight? <G>

--
Don Guillett
SalesAid Software
(e-mail address removed)

...
..

=VLOOKUP({"a","b","c","d","e"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3})

The following formula entered in a single cell returns 11.

=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}))

So do

=SUMPRODUCT(VLOOKUP({"a","b","c","d"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},
{2;3}))

and

=SUMPRODUCT(VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}))

What's your point?

Select E1:F2 and array-enter the formula

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3})

and you get

1 3
1 3

For that matter, select E1:E2 or E1:F1 and array-enter

=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d",4,40},{2;3}))

and you get 4!

Ain't VLOOKUP fun?!

I seem to catch a glimpse of a pattern here:

For the [V][H]LOOKUP functions:
The first and the third argument can both be vectors, but not at the
same time, i.e. if they are at the same time, one of them may be
replaced by a scalar equal to the first element of the vector. The
following rules seem to apply (array-enter where appropiate):

For single-cell entries:
First argument is a scalar, third argument may be a vector or a scalar.

For multi-cell entries:
First argument may be a vector or a scalar, third argument is a scalar.

It may not be precise enough for you, but I believe you get my drift.

I'm not stating, that I have the truth, the whole truth and nothing but the
truth, but as a starter, I believe it's OK.


LeoH

 

[AlfD]

But...

Let's be fair: it's as important for the intellectual bucks to loc
horns as it is for the over-lauded sports people.

Al