Array formula with column reference making macro too slow

aldousjg

New Member
Joined
Jan 20, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm new to using array formulas so I am struggling to work out how to speed up an array formula.

My array formula returns the latest referral date against each customer (column A stores the unique customer ID and column U stores referral dates):

{=MAX(IF('ACP Data'!A:A=$A2,'ACP Data'!$U:$U))}

I'm assuming it is slow because it references whole columns, but my attempt to replace A:A with $A$2:$A$3000 resulted in an #N/A error.

The array formula is stored within a formulas sheet, then copied into cell A2 in another sheet and then autofilled down to the last row which is usually about 2000/3000.

Could anyone help with this?

Thanks,
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
As you have 365 you can use the maxifs function instead
Excel Formula:
=MAXIFS('ACP Data'!$U$2:$U$5000,'ACP Data'!A$2:A$5000,$A2)
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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