Can this formula be made to run faster?

Noir

Active Member
Joined
Mar 24, 2002
Messages
362
This formula works perfectly, it's just that it runs a little slow. Does anyone have any ideas on how to speed it up?

=VLOOKUP(A2,$A$1:$A$3000,1,FALSE)&" Found in row "&MATCH(A2,Sheet2!$A$1:Sheet2!$A$3000,0)

Thanks,
Noir
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
On 2002-04-19 10:50, Noir wrote:
This formula works perfectly, it's just that it runs a little slow. Does anyone have any ideas on how to speed it up?

=VLOOKUP(A2,$A$1:$A$3000,1,FALSE)&" Found in row "&MATCH(A2,Sheet2!$A$1:Sheet2!$A$3000,0)

Thanks,
Noir

Change it to:

=A2&" Found in row "&MATCH(A2,Sheet2!$A$1:$A$3000,0)

and a question: Is the range in Sheet2 really 3000 rows deep and does it often change?
 
Upvote 0
Yes, sheets1&2 is 3000 rows deep. I am really using more rows than i need. I normally use closer to 1000 rows but, the other 2000 are there just in case.
 
Upvote 0
Noir, you should also consider "tuning" your 3,000 row list. More common entries should be sorted to the top. For example, if it was a list of salaries... one might expect 'em to be normally distributed so one might place the low and high salaries nearer the end of the list.
 
Upvote 0
On 2002-04-19 11:01, Noir wrote:
Yes, sheets1&2 is 3000 rows deep. I am really using more rows than i need. I normally use closer to 1000 rows but, the other 2000 are there just in case.

This really shouldn't matter if you experience a high "hit" rate. Exact matches are quite "expensive" if the hit rate is low.
 
Upvote 0
On 2002-04-19 11:01, Noir wrote:
Yes, sheets1&2 is 3000 rows deep. I am really using more rows than i need. I normally use closer to 1000 rows but, the other 2000 are there just in case.

OK. I'll assume column A to be alphanumeric type.

Activate Insert|Name|Define.
Enter Drecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(REPT("z",25),Sheet2!$A:$A)

Activate Add. (Don't leave yet the Define Name window.)

Enter Drange as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Sheet2!$A$1,0,0,Drecs-(ROW(Sheet2!$A$1)-1),1)

Activate OK.

Now use:

=A2&" Found in row "&MATCH(A2,Drange,0)

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top