=VLOOKUP(key,range,col[,exact])

it’s given by

=CELL(“Address”,INDEX(range,MATCH(key,INDEX(range,0,1)[,exact]),col))

]]>Cheers – Marcus

]]>As for SUM(A1:A2) and A1+A2 not returning the same result, it’s documented in online help, but I don’t have the topic reference. Text in ranges passed to functions like SUM aren’t converted to numbers. Try =SUM(A1,A2). I was careful before when I stated that automatic numeric-text conversion only occurs when expressions are used as OPERANDS to arithmetic or string operators. When they’re passed to functions, those functions have to do their own conversions or not.

FWIW, if you want =A1+A2 to return 1, set Transition Formula Evaluation. This is the way all versions of Lotus 123 (except 2.0) handle nonnumeric text as arithmetic operators. Also FWIW, if A1 contained =”100″, then A3 would still return 1 in both Excel and OOo Calc, but A4 would return 101 in Excel and still 1 in OOo Calc. Final FWIW, Gnumeric returns the same results as Excel.

]]>you’ve blown my volatility/return type idea out of the water.

Here is an eg where Excel doesn’t convert from a string to a number, or does it somewhat inconsistently.

A1 = a

A2 = 1

A3 = sum(A1:A2) = 1

A4 = A1 + A2 = #VALUE!

In Excel, in OOo A4 = 1 as well

=CELL(“Address”,INDEX(A:A,2))

=CELL(“Address”,INDIRECT(“A2”))

=CELL(“Address”,OFFSET(A2,0,0))

=CELL(“Address”,LOOKUP(1,{0;1},A1:A2))

As for Excel being pretty smart, explicit range references, the 3 built-in functions which return range references, INDEX, INDIRECT and OFFSET, and udfs returning range objects are passed as range references when used as function arguments, which is why you DON’T get syntax errors when you try to enter formulas calling SUMIF or COUNTIF functions with INDEX calls as 1st arguments whereas you DO get syntax errors when you try to enter the 4th formula above.

When you use range references as operands to arithmetic or string operators (but not the range manipulation operators space, comma and colon – I should have been precise before), Excel ALWAYS dereferences them. As for the proper data type, Excel ALWAYS (other than in Transition Formula Evaluation mode) converts text and booleans to numbers when they’re used as operands to arithmetic operators, and Excel always converts numbers and booleans to text when they’re used as operands to the concatenation operator &. Even when Excel dereferences blank cells, they’re DETERMINSITICALLY converted to text or numbers syntactically: to the number 0 when used as arithmetic operands, to text “” when used a an operand to &, and to the same type as the other operand of comparison operators.

The types Excel returns when dereferencing range references is driven exclusively by formula syntax. There’s no semantic decision tree involved.

]]>Index returns a value, or was it lookups?

Offset (and indirect) returns a range which is instantly coerced to a value, this may be a factor in them being volatile.

Excel is generally pretty smart at giving you the data type that is most appropriate for any given use.

]]>“…Lookup functions could return the address of the match rather than the value for example….”

When the Index Function is used as a stand alone function it points to a cell returns the value of a cell

Ex =Index(PickRng,Match(WhatToMtch,WhereToMatch,MatchType))

However when the Index Function is used as a portion of a Range Reference then it Points to a cell and Returns the Address of the Cell

Ex = A1:Index(A:A,Counta(A:A)) will return A1:A10 (if there are 10 Data elements in Col A without blanks)

This cannot be seen using the F9 key….but by using the Evaluate button on the Auditing Toolbar

]]>I was simply meaning the different modes – being able to flick a switch and have a version optimised for testing/checking and then flick it to production.

good point about downstream effects – what about each formula adds its address, value and (calculated) dependents to a list during the calc, or on some other trigger (TRACE?). 2 modes at once sounds like an interesting idea too, I guess that is heading back to being some pop up audit tool?

]]>