Lookup and Indirect unexpected behavior

[Lori]

While investigating ways to return values from multiple worksheets I
noticed something unusual.
In a new workbook enter some data in cells a1:c3 then try:

=LOOKUP(3,{1,2,3},INDIRECT("a1"))
=LOOKUP(3,{1,2,3},INDIRECT("a1:a2"))

On xl2002 (10.2614.2625), these formulas return the values of C1 and
A3 even though no reference is made to these cells. Does this happen
in other versions too?

 

[Herbert Seidenberg]

Happens on XL2003
You get the same results with
=LOOKUP(3,{1,2,3},A1)
=LOOKUP(3,{1,2,3},A1:A2)
It looks like third argument, A1, determines the starting point,
the default direction being to the right.
When addittional cells are given, it unambiguates the direction.
The whole range does not have to be specified.
In the first case, the third item after A1 towards the right is C1.
In the second case, the third item after A1 in the down direction is A3

 

[Lori]

The Lookup function definitely has some quirky behavior:

=INDEX(LOOKUP({1,2},{1,2},INDIRECT({"Sheet1!a1","Sheet2!a1"})),2)

crashes the application! (xl2002).
You need to use N() or T() outside LOOKUP() to make it stable.

 

[Domenic]

Not sure what you're trying to do, but I think INDIRECT, not LOOKUP,
needs to be de-referenced. For example...

=INDEX(LOOKUP({1,2},{1,2},N(INDIRECT({"Sheet1!A1","Sheet2!A1"}))),2)

Hope this helps!

 

[Lori]

Thanks that makes sense. I was using Lookup to return an array of
values across worksheets in a given order, say by switching the first
argument to {2,1}.

It does appear that you can use lookup to reorder an array like this
but it still needs to be dereferenced. This is a little annoying
because you then have to divide into separate cases for numeric and
text values.

 

[Domenic]

For numeric and text values, maybe...

=INDEX(LOOKUP({1,2},{1,2},IF(COUNTIF(INDIRECT({"Sheet1!A1","Sheet2!A1"}),
"<>*"),N(INDIRECT({"Sheet1!A1","Sheet2!A1"})),T(INDIRECT({"Sheet1!A1","Sh
eet2!A1"})))),2)

Hope this helps!

 

[Lori]

Here's an alternative method which will return either text or numeric
values across sheets I've just discovered. It uses one of the only
useful things about database functions in my opinion - that they can
dereference arrays.

=INDEX(DGET(OFFSET(INDIRECT({"sheet1!a2","sheet2!a2"}),-1,0,2),
1,C1:C2),2)

The criteria cells C1:C2 are just blank or unrelated to Sheet1!A1 and
sheet2!A1.