Find Max value

[Guest]

Please help with formula
In the WO$ cell on worksheet 1, I need it to look on worksheet 2, match the
project number, find the highest revision and return the $$$ amount.
Worksheet 2 is not sorted in any specific way.

WS1

Project Description WO$
1 Test 1
2 Test 2
3 Test 3

WS2

Project Revision $$$
1 1 1,000
2 1 2,000
3 1 2,000
2 2 5,000
2 3 10,000
1 2 6,000
3 2 8,000

WS1 Result would be

Project Description WO$
1 Test 1 6,000
2 Test 2 10,000
3 Test 3 8,000

 

[Scott]

Try the following, entered with CTRL+SHIFT+ENTER:

=MAX((Sheet2!$A$2:$A$8=A2)*Sheet2!$C$2:$C$8)

Scott

 

[Scott]

Oops... I can't read. Highest revision, not $$$.

=SUMPRODUCT(--(Sheet2!$A$2:$A$8=A2),--(Sheet2!$B$2:$B$8=MAX((Sheet2!$A$2:$A$8=A2)*Sheet2!$B$2:$B$8)),Sheet2!$C$2:$C$8)

Scott

 

[Guest]

Thanks for the quick reply.
I left out a valuable piece of information, very sorry!!!
Sometimes a revision to the work order can result in a lower number, that's
why I need it to look at the revision number and give the the total for the
highest revision number of that project.

Does that make better sense?

Thanks again,

Terri

 

[Bob Phillips]

=INDEX(Sheet2!$C$1:$C$20,MAX(IF(Sheet2!$A$2:$A$20=A2,ROW(Sheet2!$B$2:$B$20))
))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Thanks,

I tried this a couple of times and it is giving me the highest revision not
the dollar amount of the highest revision.

 

[Guest]

Cool that works, thanks a bunch for the quick and very helpful reply.

T