Can this formula be made to run faster?

[Noir]

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

[Aladin Akyurek]

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?

[Noir]

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.

[Mark W.]

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.

[Mark W.]

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.

[Aladin Akyurek]

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

[Noir]

Thanks for your help Mark!!!

[Noir]

Thanks Aladin!!!