LOOKUP cells from different worksheets

[Soo Ting]

hello. does anyone know how to perform a vlookup for
arrays from different worksheets?

i read through the help guide and i believe that this is
known as a 3D reference and cant be done on table arrays.
is this true?

in that case, does any know any other method? such as to
create a macro and etc?

thanks in advance!

 

[Frank Kabel]

Hi
try:
=VLOOKUP(value,'other_sheet_name'!A1:B100,2,0)

 

[Aladin Akyurek]

=VLOOKUP(A3,THREED(Sheet1:Sheet3!$A$2:$B$5),2,0)

looks up A3 in A2:A5 on Sheet1 thru Sheet3.

THREED requires that you install the morefunc.xll add-in

 

[Peo Sjoblom]

Add-ins

http://www.rhdatasolutions.com/morefunc/

there's a link for download and descriptions, the function you are looking
for is THREED
then combine it with
VLOOKUP

here's a link directly to the author's site

http://longre.free.fr/english/

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Soo Ting]

hello.

thanks for the help. i downloaded the add ins and it worked....
UNTIL... my vlookup function came to Sheet2... it didnt work then..
this is what ive keyed in:

=VLOOKUP($J$11,THREED(N1:'N14'!$A$16:$Z$100),3,0)

N1 to N14 are the names of the sheets, i suspected it was the 'N14
that fails the function. however, i didnt type that, everytime i trie
to remove it from the formula, it appears again as i press on th
"enter" key!

any idea?!

thanks thanks thanks...
help appreciated....

Regards
Soo Tin

 

[Aladin Akyurek]

N1,...N14 are unfortunate names. If you'd like too keep them as is, then:

Insert 2 new sheets, name them First and Last, put all the relevant sheets
between these two, and invoke:

=VLOOKUP($J$11,THREED(First:last!$A$16:$Z$100),3,0)

Since you need just 3 columns of the relevant ranges, it's certainly better:

=VLOOKUP($J$11,THREED(First:last!$A$16:$C$100),3,0)

 

[Soo Ting]

thanks ppl, u have no idea how much time ive saved from all thes
repetitive actions, if i were to link them manually....

thanks again...

ps: can i ask some programming questions here as well? cos it direct
link to the thing im doing now...

cheers
sootin