Speed Up Index or Match Formula on 5 Lakhs Rows

[Kaustubh1040]

I have an excel consisting of 5 lakhs rows and four columns. Now, from this file, I am trying to pull data for particular product with the help of Index and Match formula.

My Problem is: Using Index and Match formula in 5 lakhs line items is too time consuming. Instead is there any ways to speed up this process.

For more details please refer to below example.

Code Product Type Value
12345 Product A PAC 1
12346 Product A KG 2
12347 Product A CFC 3
12348 Product B PAC 1
12350 Product B KG 3
12351 Product B CFC 2
12352 Product C KG 12
123456 Product C PAC 12


From above table I want to find the value of Product A for CFC

Product A CFC Value will be ???/

P.S: There are more than 5 lakhs product names.

How to speed up this process?

It is possible to filter the data for Product A and then use Index Match formula on filtered rows only?

Please suggest

[AliGW]

What is 5 lakhs???

[Kaustubh1040]

0.5 millions rows. In India, lakhs is used a substitute of millions.

[AliGW]

OK - please bear in mind in future that the lingua franca on this forum is English (and I am saying this for practical reasons).

Please post the formula you are using.

Excel's maximum rows is 1,048,576 now, but only 65,536 in your version.

[Kaustubh1040]

Ok..

Here is my formula (considering above example): =INDEX(D1:D9,MATCH(1,(E13=B1:B9)*(F13=C1:C9),0))

I will replace '9' will row count while using on millions of rows.

I am using MS Excel 2016 on my laptop.

Note: E13 include Product A
F13 include CFC

[AliGW]

Please update your forum profile with your current version of Excel.

Your maximum row number is 1,048,576.

[Kaustubh1040]

Done updated the version I am using.

[AliGW]

Did you understand the comment about the maximum number of rows?

[Kaustubh1040]

Yes, In 2016, the numbers of rows are greater than the older version of MS Excel. Thanks for highlighting this. I was not aware that I have mentioned the old version of MS excel in my profile page. Thanks once again.

[AliGW]

But it's still not enough to accommodate 5 lakhs, is it? Not a lot you can do without dividing your data up into manageable chunks.

[Kaustubh1040]

Thanks I will delete the data into two parts. Thanks for replying to my question. And, the total numbers of rows are 502027.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[MarvinP]

If you are trying to pull a subset of your rows, you could use Power Query on your data. It only looks at the first few hundred rows and then will filter your data as it comes into the workbook.

https://www.masterdataanalysis.com/m...records-excel/

[PietBom]

You can have a super fast search routine.
1. Make a key-field concatenate Product and Type
2. Sort the table ascending on this key-field
3. Input your search values for Product and Type
4. Make a search string combining Product and Type
5. Use a combined formula Vlookup and Match to find the row with the matching key
6. Extract your results using the Index-formula

Try it, you will be surprised !
Even with 500k+ records.