Large Data - Best way to do vlookup /index match (or alternative)

[Chelsea0270516]

Hello

I work with very large data sets in excel. Part of the process is using vlookups to pull information from our system about existing product min/maxes and compare it to planned min/maxes. The files I am working often have 34k-70k lines of data (that is all needed). Is there a better pull information from those sheets besides vlookups/index match?

As I type I am waiting on a file to finish pasting the values from a vlookup (I've been working on populating 5 columns for over 2 hours).

Also is Index Match actually any faster with a large data set? I've been looking into it, but almost no one uses it in my workplace & I don't want to confuse the people looking at my files unless the speed is significantly better.

I'd love to find some alternative method but everything I've seen so far to make large file look ups go faster requires that you are only looking to confirm the item is in the data set while I need specific information from the set. I am comfortable using VBA if that could speed up the process a bit.

Also I love videos/blogs if you have any that would be a good reference I'd love to see them.

 

[Michael M]

Autofilter, Pivottables and VBA might be options, but you'd need to provide a small data sample AND a sample of what you are trying to extract !!

 

[Chelsea0270516]

First is the sample of the file I am vlookup'ing in. Columns B C D E & F are looked up using column A & the second spread sheet. The items that are being looked up in the second file match the names in Row 2.

Since the entries in both tables are unique I don't think a pivot table would help too much.

Thoughts?

Let me know if I wasn't super clear.

 

[Michael M]

Hmmm.....If the store SKU's are unique, why are the returned results on sheet 1 different to that of sheet 2 ?
How many columns are in Sheet2 ?
What are the VLOOKUP formulae on Sheet 1 ?
Are the sheets in the same workbook ??
Will the store SKU already be loaded on Sheet 1 in column "A" ?