Index with Match and Min/Max

[reberryjr]

I'm trying to get the max value from column AG on the Stats worksheet, where the value in column C of the Client Summaries Sheet matches the value of column C on the Stats sheet. From the couple of examples I've seen online, that actually list all 3 parts, this is coded correctly, but I'm getting a #N/A error. Would someone mind showing me what I have mixed up here?

=INDEX(Stats!AG:AG,MATCH(MAX('Client Summaries'!C:C),Stats!C:C,0))

 

[Takae]

If columnC of Stats sheet doesn't have the result of "MAX('Client Summaries'!C:C)", it says #N/A.

 

[reberryjr]

But the value does exist in column C, of both sheets.

 

[Takae]

column C of stats sheet is not sorted ascending order?

 

[reberryjr]

I actually JUST finished working through it some more and found my solution. The end goal was to enter the formula via VBA, when a cmd button was clicked. When I posted the question, I was trying to create the formula in excel, which I was finally able to do. When I posted this last night, I was very tired and this morning, realized that I wasn't referring to a specific cell to find the match on, so I just switched to an IF statement. Then, I just converted it to the VBA equivalent.

ws1.Range("M" & LastRow1 + 1).FormulaArray = "=MIN(IF(Stats!C[-10]='Client Summaries'!RC[-10],Stats!C[-4]))"