Thanks:  0
Likes:  0

1. 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

2. 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?

3. 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.

4. 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.

5. 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.

6. 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)

7. Thanks for your help Mark!!!

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•