VLookup queries

[Rodney]

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.

 

[Frank Kabel]

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)))

 

[Rodney]

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.

--
(e-mail address removed)
(Remove gum to reply)


| 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)))
|
|
|
| --
| Regards
| Frank Kabel
| Frankfurt, Germany
|
| | > 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.
| >
| > --
| > (e-mail address removed)
| > (Remove gum to reply)
| >
| >
| >
| >
|