How to optimize Index Match , for large sets of data

**asweare** · 12-28-2018, 08:36 PM

Hello
I have a working formula but I now want to use it to search across a large dataset so I really need to optimize the formula.

the formula is like this,

Ref	Name	Height	Strength	Weight
1	A	150	120	60
2	B	150	120	60
3	C	110	10	60
4	D	180	200	80

so when i use table, searching for Height, by Ref number I get the answer I want perfectly. but
when I am using with Really Really large data, it's really slowing me down. is there any kind of VBA code , or just excel formula i can make this process faster.
its keep lagging down,

the formula was like this.
=index([Height],match([Ref],Search cell number)

please help thanks all for reading my post

**Richard Buttrey** · 12-28-2018, 09:19 PM

Hi and welcome to the forum,

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

**asweare** · 12-28-2018, 09:46 PM

Hi I would love to put the sample excel file in but I am not allow to do it yet. until i made more post

**Richard Buttrey** · 12-28-2018, 10:11 PM

I didn't know that. Does the system say how many posts you should have?

**asweare** · 12-29-2018, 12:55 AM

well no..

if u send me email address i can send you a link

**Glenn Kennedy** · 12-29-2018, 05:32 AM

You won't be able to post links until you have 10+ posts. However, you can attach the file directly.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**asweare** · 12-29-2018, 06:35 AM

thank you so much for the help.

i am looking for more than 100,000,000 row.

it's really long,

I am trying to upload the file, but it doesn't allow to do it....

let me try to re write my question again.

>>Original Data
Ref Name Height Strength Weight
1 A 150 120 60
2 B 150 120 60
3 C 110 10 60
4 D 180 200 80

>> Reference data
Ref Height
3 110 (=index(OrignalData(Height)Match(ReferenceData Ref,Original Data Ref)
4 180

by Writing like this, I was able to get the reference data,
This is working great when the data is small,
but it's keep getting bigger and it's keep slowing down.

thanks for taking time to read it.

**AliGW** · 12-29-2018, 06:36 AM

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**Glenn Kennedy** · 12-29-2018, 06:43 AM

How can you have more than 100,000,000 rows when Excel has a maximum of 1,048,576 rows????

**asweare** · 12-29-2018, 07:34 AM

Thanks for the help,

well because I am not just using only one row,
I use multiple rows and sheets,
I am guess probably more...

I make all sorts of data by using reference.
you can look at the data I made in the file.
I use this to make lots of data for the game i am creating.

once again thanks for the support and help

**Glenn Kennedy** · 12-29-2018, 08:23 AM

Your sample is protected!! Your formula (based on your sample) could be shortened to:

=INDEX(표1[Height],표1['#])

**AliGW** · 12-29-2018, 08:28 AM

Glenn - which bit is protected? I don't seem to be seeing that here.

**Glenn Kennedy** · 12-29-2018, 08:39 AM

Yea. Ignore me. I was being thick...

**AliGW** · 12-29-2018, 08:40 AM

Lol!!!

**asweare** · 12-29-2018, 09:04 AM

thanks for the help,
just using index
seems to be working, but if reference data isn't in order, it seems it's not working as i wanted.
so I made another sample.

thank you so much for the help.

**AliGW** · 12-29-2018, 09:17 AM

I think you need this:

=INDEX(Reference[Height],MATCH(B13,Reference['#],1))

HOWEVER, the lookup table needs to be in ascending order thus:

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G
2	>>Reference Data
3	#	Name	HP	Height	Strength	Weight
4	1	10	15	100	50	5
5	10	100	105	1000	500	50
6	19	190	195	1900	950	95
7	30	300	305	3000	1500	150
8	50	500	505	5000	2500	250
9	60	600	605	6000	3000	300

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	B	C
11	>>Data
12	#	Height
13	10	1000
14	1	100
15	11	1000
16	10	1000
17	50	5000
18	60	6000
19	19	1900
20	30	3000

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	C
13	=INDEX(Reference[Height],MATCH(B13,Reference['#],1))

Sheet: Sheet1

**asweare** · 12-29-2018, 11:54 AM

thank you so much for helping me out.
I tried this method already,
using Index and Match..
when I use more data, it really start to get slow..
is there any other method i can use?

**Glenn Kennedy** · 12-29-2018, 12:04 PM

Withdrawn by GK.

**Richard Buttrey** · 12-29-2018, 12:46 PM

This has been reported as cross postes and as such does not comply with Rule 3 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Please make sure to include the full link, so that other members will be permitted to continue helping you

Note that if you have fewer than 10 posts, you may need to type the link, or past it in pieces (or ask a mod to do that for you)

As mentioned earlier I am also closing the thread since you have started another.

How to optimize Index Match , for large sets of data

LinkBack

Thread Tools

Display

How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Re: How to optimize Index Match , for large sets of data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] INDEX and MATCH with structured references and from two data sets

[SOLVED] compare two large data sets on mulitple tabs and delete rows that match

[SOLVED] Large Data - Index Match Match with another function?

Index and Match formulas for three different data sets

[SOLVED] How to optimise INDEX(MATCH()) for large data sets?

[ASK] How to Optimize function in Google Sheet for large data

Index Match Rows for multiple data sets

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions