VLOOKUP Query

Paul McNeilly

unread,

Sep 13, 2002, 11:24:51 AM9/13/02

to

I'm using VLOOKUP, but the table which is being used for the Table_array is
too big to fit onto one sheet, so I have to split it onto 2 sheets.
Is there anyway I can use VLOOKUP to look through both sheets, I can only
seem to make it look through one sheet at a time.

Any help would be appreciated.

Cheers
Paul

Don Guillett

unread,

Sep 13, 2002, 11:34:09 AM9/13/02

to

design your formula so that
=if lookup in table1 is no good,lookup in table 2

--
Don Guillett
SalesAid Software
Granite Shoals, TX
don...@281.com
"Paul McNeilly" <pa...@hartww.com> wrote in message
news:e6IHVnzWCHA.1852@tkmsftngp10...

Dick Kusleika

unread,

Sep 13, 2002, 11:38:19 AM9/13/02

to

Paul

You can change your formula to look in the second table if it's not found in
the first, such as

=IF(ISNA(VLOOKUP(A1,FirstTable,2,FALSE),VLOOKUP(A1,SecTable,2,FALSE),VLOOKUP
(A1,FirstTable,2,FALSE)))

--
Dick Kusleika
MVP - Excel

Post all replies to the newsgroup.

"Paul McNeilly" <pa...@hartww.com> wrote in message
news:e6IHVnzWCHA.1852@tkmsftngp10...

Paul McNeilly

unread,

Sep 13, 2002, 12:05:20 PM9/13/02

to

Thanks for your help Dick, I'll give it a try.

Cheers
Paul

"Dick Kusleika" <di...@paragonconstructioninc.com> wrote in message
news:##wrNwzWCHA.1004@tkmsftngp08...

Aladin Akyurek

unread,

Sep 13, 2002, 12:58:35 PM9/13/02

to

If you (and your users) install morefunc.xll, you can use something like:

=VLOOKUP(A1,THREED(Sheet1:Sheet2!$F$2:$G$5),2,0)

If you want to avoid #N/A's...

=IF(ISNA(SETV(VLOOKUP(A1,THREED(Sheet1:Sheet2!$F$2:$G$5),2,0))),"",GETV())

Morefunc.xll is available from: http://longre.free.fr/english/index.html

Aladin

"Paul McNeilly" <pa...@hartww.com> wrote in message
news:e6IHVnzWCHA.1852@tkmsftngp10...

RagDyer

unread,

Sep 13, 2002, 9:31:23 PM9/13/02

to

Aladin,

Will those work on closed WBs?

Regards,

Rd

"Aladin Akyurek" <aky...@xs4all.nl> wrote in message
news:alt5jn$7i5$1...@news1.xs4all.nl...

Aladin Akyurek

unread,

Sep 14, 2002, 3:24:59 AM9/14/02

to

> Will those work on closed WBs?

Alas, the answer is no.

Aladin

"RagDyer" <Phn...@pacbell.net> wrote in message
news:uqz7M64WCHA.2452@tkmsftngp09...

Ragdyer

unread,

Sep 14, 2002, 10:54:23 AM9/14/02

to

Pursuant to our conversations on calculation speed and efficiency, I went
through one of my old, very large dB WSs a short time ago, modifying the
double Vlookup error checking formulas and transforming them to the Countif
method.
I was about half way through it when I received the first call for help from
one of the office people, saying things were not working.

Darned Countif doesn't work on closed WBs.

Had to change over to an ISnumber-Match-Index formula, which seems to work
in all conditions, which was a "saver" from one of your posts.

ANYHOW, this makes me think about this OP having a list sooo large, that it
doesn't fit on a single sheet ... and he's going to perhaps use a
TRIPLE-Vlookup!!!

Wonder how long it will take for his WS to calculate?

Actually, this might be a good test if we could get him to try and compare:

=IF(ISNUMBER(MATCH(A1,INDEX(List1,0,1),0)),INDEX(List1,MATCH(A1,List1Col1,0)
,2),INDEX(List2,MATCH(A1,List2Col1,0),2))

I would think there would have to be a significant difference in calculation
speed.

Regards,

RD

"Aladin Akyurek" <aky...@xs4all.nl> wrote in message

news:aluoca$stc$1...@news1.xs4all.nl...

Aladin Akyurek

unread,

Sep 15, 2002, 2:46:53 AM9/15/02

to

Yeah. ISNUMBER+MATCH will be faster than COUNTIF (By the way, Paul McNeilly
(the OP) doesn't
appear to have to work with a closed workbook.). It would be interesting
indeed to see whether the INDEX+MATCH formula
you suggest would operate faster than one with triple VLOOKUP.
I'd like also to see what difference it makes if we use the relevant
functions
from morefunc.xll (THREED,SETV,GETV) either in combination with VLOOKUP or
the formula you suggest:

=IF(ISNUMBER(SETV(MATCH(A1,INDEX(List1,0,1),0))),INDEX(List1,GETV(),2),INDEX
(List2,MATCH(A1,List2Col1,0),2))

Aladin

"Ragdyer" <RAG...@MSN.COM> wrote in message
news:OYeD65$WCHA.2644@tkmsftngp10...

> Pursuant to our conversations on calculation speed and efficiency, I went
> through one of my old, very large dB WSs a short time ago, modifying the
> double Vlookup error checking formulas and transforming them to the
Countif
> method.
> I was about half way through it when I received the first call for help
from
> one of the office people, saying things were not working.
>
> Darned Countif doesn't work on closed WBs.

[Aladin] Hope Peo will read this and switch from COUNTIF to ISNUMBER+MATCH!