VLookup queries

  • Thread starter Thread starter Rodney
  • Start date Start date
R

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.
 
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)))
 
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)
| >
| >
| >
| >
|
 
Back
Top