HELP with ARRAY Formula

1. HELP with ARRAY Formula

Hi,
I have an array formula using INDEX/MATCH to provide me the value when there are two criterias. (This formula is needed for column D on the 'StatTbl' tab)

In the attached, I have finding the value of the 'Edit Date' when:

New Value (column G) = Tech Review
AND
Case Number (column A) on the Raw Data tab = UniqueCase# on the StatTbl tab

The formula to provide the above is:

{=INDEX(RawData[Edit Date],MATCH(1,("Tech Review"=RawData[New Value])*([@[UniqueCase'#]]=RawData[Case Number]),0))}

What I want to find is, the MAX date when the formula looks for the "Tech Review"=RawData[New Value].
There are 2 situations that the New Value on the RawData tab has 2 dates for the same Case Number. (40738 & 53022).
Right now the formula is generating the first date for those case numbers. I need the 'MAX' date.

I cannot figure out the syntax to find this value.

All help would be beneficial.
Thanks

2. Re: HELP with ARRAY Formula

StatTbl

C2=IFERROR(IF(AGGREGATE(14,6,(RawData!\$H\$2:\$H\$2000)/(RawData!\$G\$2:\$G\$2000=StatTbl!C\$1)*(RawData!\$A\$2:\$A\$2000=StatTbl!\$A2),1)=0,"",AGGREGATE(14,6,(RawData!\$H\$2:\$H\$2000)/(RawData!\$G\$2:\$G\$2000=StatTbl!C\$1)*(RawData!\$A\$2:\$A\$2000=StatTbl!\$A2),1)),"")

copy across and down

3. Re: HELP with ARRAY Formula

Thank you very much! This worked!

I am familiar with the AGGREGATE function, but do not understand how the '/' works in the solution. What exactly does that do?

thanks again!

5. Re: HELP with ARRAY Formula

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

6. Re: HELP with ARRAY Formula

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1