Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and Indirect unexpected behavior
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and Indirect unexpected behavior
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and Indirect unexpected behavior
The Lookup function definitely has some quirky behavior:
=INDEX(LOOKUP({1,2},{1,2},INDIRECT({"Sheet1!a1","S heet2!a1"})),2) crashes the application! (xl2002). You need to use N() or T() outside LOOKUP() to make it stable. On Feb 9, 10:54 pm, "Herbert Seidenberg" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and Indirect unexpected behavior
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! In article .com, "Lori" wrote: The Lookup function definitely has some quirky behavior: =INDEX(LOOKUP({1,2},{1,2},INDIRECT({"Sheet1!a1","S heet2!a1"})),2) crashes the application! (xl2002). You need to use N() or T() outside LOOKUP() to make it stable. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and Indirect unexpected behavior
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. On Feb 10, 4:34 pm, Domenic wrote: 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! In article .com, "Lori" wrote: The Lookup function definitely has some quirky behavior: =INDEX(LOOKUP({1,2},{1,2},INDIRECT({"Sheet1!a1","S heet2!a1"})),2) crashes the application! (xl2002). You need to use N() or T() outside LOOKUP() to make it stable.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and Indirect unexpected behavior
For numeric and text values, maybe...
=INDEX(LOOKUP({1,2},{1,2},IF(COUNTIF(INDIRECT({"Sh eet1!A1","Sheet2!A1"}), "<*"),N(INDIRECT({"Sheet1!A1","Sheet2!A1"})),T(IN DIRECT({"Sheet1!A1","Sh eet2!A1"})))),2) Hope this helps! In article . com, "Lori" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and Indirect unexpected behavior
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!a 2"}),-1,0,2), 1,C1:C2),2) The criteria cells C1:C2 are just blank or unrelated to Sheet1!A1 and sheet2!A1. On Feb 10, 6:57 pm, Domenic wrote: For numeric and text values, maybe... =INDEX(LOOKUP({1,2},{1,2},IF(COUNTIF(INDIRECT({"Sh eet1!A1","Sheet2!A1"}), "<*"),N(INDIRECT({"Sheet1!A1","Sheet2!A1"})),T(IN DIRECT({"Sheet1!A1","Sh eet2!A1"})))),2) Hope this helps! In article . com, "Lori" wrote: 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.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT | Excel Worksheet Functions | |||
INDIRECT lookup of sheet names | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
indirect lookup | Excel Worksheet Functions |