CODEWITHSUNDEEP
vbaexcelpivot-tablenested-if
PootyToot
PootyToot

Reputation: 329

How to write MAX IF formula in VBA to achieve a lookup on two values

I have been using an array formula to look up a resource name entry located in Column Y, look up the project name in Column B and return the latest / highest date value in Column AE using the MAX function.

This is now to put it bluntly, killing my spreadsheet due to the sheer size of the data in the spreadsheet (60,000 lines). Formula is: 

{=IF(Y3="","",IFERROR(MAX(IF(Y$2:$Y$60000=Y3,IF($B$2:$B$60000=B3,$AE$2:$AE$60000))),""))}

If we look at the table below, the formula will return 21/04/2014 for Richard, and 21/06/2014 for Fred, whom are both working on the project Capitol.

   B     |     Y     |     AE
Capitol    Richard     21/03/2014
Capitol    Richard     21/04/2014
Capitol      Fred      21/05/2014
Capitol      Fred      21/06/2014

I am wondering if there is a way to re-write this formula in VBA in order to get the same result, but run it with more efficient memory? I am a beginner in VBA but slowly learning.

Upvotes: 1

Views: 929

Answers (1)

pnuts
pnuts

Reputation: 59475

A PivotTable would return the results you mention:

SO26248906 example

Upvotes: 1

Related Questions