Hello. I'm trying to do a match formula for 400,000 rows. Is there a better formula? if(match(h2,$d$2:$d$420,0),yes, no). Thanks.
Hello. I'm trying to do a match formula for 400,000 rows. Is there a better formula? if(match(h2,$d$2:$d$420,0),yes, no). Thanks.
Are the search values sorted in either ascending or descending order? If they are or can be sorted, then you can use a much more efficient binary search algorithm by specifying the 3rd argument as 1 (for data sorted ascending) or -1 (for data sorted descending).
If they are not and cannot be sorted, then that might be as good as it gets in Excel. I don't know if a more robust database program might be able to perform the same linear search faster than Excel.
Originally Posted by shg
You would normally use ISNUMBER with that, and you can use full-column references, so try this:
=IF(ISNUMBER(MATCH(H2,$D:$D,0)),"yes","no")
You could also do it this way:
=IF(COUNTIF($D:$D,H2),"yes","no")
Hope this helps.
Pete
@ Mr. Shorty. I sorted into descending order and then wrote this =IF(ISERROR(MATCH(H288804,$D$2:$D$466271)),0,1). It works faster but it's not getting the matches right. It's returning 0's when there are in fact matches.
Hi Peter. Those formulas work but they don't fix the speed problem. It bogs my excel down to where it doesn't function.
Review help file for MATCH() function: https://support.office.com/en-us/art...9-533f4a37673a Note that, if the optional 3rd argument is not specified, that it assumes a value of 1 for that argument, which only works for data sorted in ascending order. If you want to have your data sorted in descending order, be sure to specify -1 for that argument.
@ Mr. Shorty. Got it. Still incredibly slow unfortunately
I suspect you're still not using the correct formula.
Entia non sunt multiplicanda sine necessitate
Try this:
Please Login or Register to view this content.
Pretty sure that I am. I used Pete_UK formula, put both columns into descending order, and then used a -1 for the third option. There are 400,000 rows.
I suspect something else then. I can fill an entire column (1E6 rows) in descending order and my MATCH() function is near instantaneous. Are you certain the search is the bottleneck, or could there be something else slowing your sheet down?
@Mr. Shorty, would you mind attaching your spreadsheet for me to check? Thanks.
This is the formula I'm using =IF(ISNUMBER(MATCH(F466300,$A$2:$A$463252,-1)),1,0)
Data is descending in both columns.
I don't have Excel on this computer, but it is a very easy spreadsheet to build from scratch.
1) Start with a new sheet
2) In A1 enter =ROW(A$1048576)
2a) In A2 enter =A1-1
3) Copy/fill A2 down to the bottom of the column.
3a) Or any other formula combination that will give you a descending list of numbers in column A
4) In C1 enter =RANDBETWEEN(0,1050000)
5) In D1 enter =MATCH(C1,A1:A1048576,-1)
5a) If desired, include the IF(ISNUMBER(...)...) parts of the function, since they should contribute insignificantly to the computation time.
Press F9 and see how long it takes to calculate.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks