VLookup queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rodney - 07 Apr 2004 06:45 GMT

I have my VLookup happening
querying an array of 25,000 records.

I want to beef it up to 150,000
Can VLookup query across 3 sheets of the workbook?

I employ this statement:
"=IF(ISERROR(VLOOKUP(B46063,$B$2:$C$11494,2,FALSE)),"x",(VLOOKUP(B46063,$B$2:$C$11494,2,FALSE)))

Why do some result cells show a nought 0 instead of "x" ?

When I paste the results back to MSWorks
I have to "paste special" (unformatted text)
before it will transfer successfully to the Works cells

Any workaround?

Thanks for any assistance.

--
rodney@touch88gum.com.au
(Remove gum to reply)

Reply to this Message

Frank Kabel - 07 Apr 2004 07:56 GMT

Hi
The zero could leed from an empty cell in column C but a valid entry in
column B

For your other questions to use more than one sheet:
- not possible directly with VLOOKUP
- You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. This may work (the help of this function at least
suggested it). Note: I haven't tried this in much detail -> so no
guarantee it will work. e.g. you may use the following formula
=IF(ISERROR(VLOOKUP(B46063,THREED('sheet2:sheet3'!B1:C9999),2,FALSE)),"
x",(VLOOKUP(B46063,$THREED('sheet2:sheet3'!B1:C9999),2,FALSE)))

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> I have my VLookup happening
> querying an array of 25,000 records.
[quoted text clipped - 3 lines]
>
> I employ this statement:

"=IF(ISERROR(VLOOKUP(B46063,$B$2:$C$11494,2,FALSE)),"x",(VLOOKUP(B46063
,$B$2:$C$11494,2,FALSE)))

> Why do some result cells show a nought 0 instead of "x" ?
>
[quoted text clipped - 9 lines]
> rodney@touch88gum.com.au
> (Remove gum to reply)

Reply to this Message

Rodney - 07 Apr 2004 09:01 GMT

Thanks Frank,
I'll give it a go, although I am only just learning to drive this SS.
(I turn the ignition, switch on the lights, and the horn beeps!)

In the mean time, it is no hassle I guess
to open subsequent workbooks for each block
of 50,000 records in the array.

Thanks also for the formula, a question I would have
bounced back with.

Regards to you also.

--
rodney@touch88gum.com.au
(Remove gum to reply)

| Hi
| The zero could leed from an empty cell in column C but a valid entry in
[quoted text clipped - 39 lines]
| > rodney@touch88gum.com.au
| > (Remove gum to reply)

Reply to this Message