Try...

=INDEX(Col_A,MATCH(LARGE(IF(RIGHT(Col_A,5)="Total" ,IF(LEFT(Col_A,5)<"Gra
nd",IF(MATCH(Col_A,Col_A,0)=S+1,Col_B-S/10^5))),ROWS(D$2:D2)),Col_B-S/10^
5,0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article .com,
"Fin Fang Foom" wrote:

Hi Domenic, ThankYou! very much.

Thank You for repyling. I was really closes on my end I was still
using the ROW Function but in your formula you didn't, that was the
key. Also I had to take out the -1 in the formula to get the correct
results. The value I'm returning is text.

Another question, I dont know this is possible.

When using the formula it returns the text value that has the highest
qty. But if the text value say part# 20-T it will come out maybe 3
times because that part number is in multiple worksheets. Is there a
way that the formula do a SUMIF(LARGE( and return the text value just
once?

Let me know I made it clear.