VLOOKUP error

[Chris Davison]

Mornign All,

I'm getting an error on the following :

=VLOOKUP(2,{1,"Chris";2,now()},2,0)

am I right in thinking I'm not allowed to use ANY volatile functions in arrays ?

thanks
Chris

 

[Yogi Anand]

I believe you can not use any function in an array!

 

[Aladin Akyurek]

On 2002-04-13 16:35, Chris Davison wrote:
Mornign All,

I'm getting an error on the following :

=VLOOKUP(2,{1,"Chris";2,now()},2,0)

am I right in thinking I'm not allowed to use ANY volatile functions in arrays ?

thanks
Chris
/board/images/smiles/icon_smile.gif

Chris,

A constant array cannot have cell refs or computations. Your

{1,"Chris";2,now()}

includes a computation, that is, NOW().

Aladin

 

[Chris Davison]

hummph

thanks Guys

Aladin, you said "constant" array

are there other types I could use that would allow such a function as now() ?

ta
Chris

 

[daleyman]

whats stopping you from putting these values and formula into a cell range and doing a standard vlookup?

 

[Chris Davison]

Daleyman.... wow we're all up late !!

I'm still farting around with an earlier thread about lottery numbers generated from a single formula

if you're curious : http://www.mrexcel.com/board/viewtopic.php?topic=3658&forum=2 but it's a bit boring now LOL

 

[Yogi Anand]

On 2002-04-13 16:35, Chris Davison wrote:
Mornign All,

I'm getting an error on the following :

=VLOOKUP(2,{1,"Chris";2,now()},2,0)

am I right in thinking I'm not allowed to use ANY volatile functions in arrays ?

thanks
Chris

Hi Chris:
As I mentioned in my post above I believe you can not use any FUNCTION, or even a CELL REFERENCE in an array. One can however, like in your instance use the INDIRECT FUNCTION ...

=INDIRECT(VLOOKUP(2,{1,"Chris";2,"E19"},2,0))

where E19 houses =NOW()

and I did get the result as 4/13/02 7:43 PM

Keep up the good work buddy!

 

[Chris Davison]

thanks Yogi mate

if you're familiar with the above link, you'll be aware of the constraints.... no outside cell references......single cell formula only

sorry, I didn't want to drag up this old chestnut per se, I was just tinkering around with arrays

 

[Aladin Akyurek]

On 2002-04-13 16:41, Chris Davison wrote:
hummph

thanks Guys

Aladin, you said "constant" array

are there other types I could use that would allow such a function as now() ?

ta
Chris

No. One needs constructor functions that would allow computing the array first then evaluate the formula of which the computed array is/becomes a part.

 

[Chris Davison]

okie dokie...thanks Aladin...

confirmation that a path is closed is equally as useful as other suggestions

much appreciated, as always
Chris