• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup in Arrays

AAP

AAP

Member
Hi, I try to test a vlookup in two arrays.
Array1 = An excel file where lookup_value is given and need to find rest of the columns from Array2.
Array2 = A database in access.
When I try to read both arrays. I get
Array1 = for example (0,200 To 0,4)
Array2 = for example (0,20 To 0,70000)

If you can see problem that from excel Array1 shows 200 rows and 4 columns but Array2 shows 20 rows and 70000 columns but actually Array2 (or database) is 70000 rows and 20 columns. That's the reason I cannot run a vlookup between these two arrays. Is there any solution you can see.
 
Personally I would probably run a query joining the two tables together and then just drop the resulting recordset into the worksheet.
 
Debaser said:
Personally I would probably run a query joining the two tables together and then just drop the resulting recordset into the worksheet.
Click to expand...

Array1 is not a fixed excel book or worksheet. It changes frequently. Therefore this option does not fit in my needs.
 
Why? If the data is in a worksheet, you can query it.
Mind you, you haven't actually given a good reason why you can't do a lookup between the arrays.
 
Debaser said:
Why? If the data is in a worksheet, you can query it.
Mind you, you haven't actually given a good reason why you can't do a lookup between the arrays.
Click to expand...

Array's behave differently when reading from an excel file and reading from a database. That's the reason I have this:
Array1 = for example (0,200 To 0,4)
Array2 = for example (0,20 To 0,70000)
And you cannot run vlookup on this.
 
No they don't. Arrays are arrays and always behave the same.

If you're using getrows to load a recordset into an array it will be transposed, yes, but that's a quirk of getrows not of arrays. All you need to do is either transpose that array or use hlookup instead of vlookup.
 
However, if your arrays have 70000 elements, worksheet functions won't work on them unless you load them onto a worksheet anyway.
 
You must log in or register to reply here.
Back
Top