Error using MATCH formula with LARGE and IF arguments

[CMTR]

Hello all!

I want my formula to yield the value associated to the top 3 highest values in a column when evaluating only certain rows.

My best attempt does not yield something that makes sense:

=MATCH(IF(B2:B26="Consultants",LARGE(C2:C26,1),0),C2:C26,0)

Using the example with the 3 projects below (project # are in column A), i would want the formula to:
(1) Look only for "Consultants" costs in column B;
(2) Find in column C the highest value associated to the "Consultants" rows;
(3) Return the project number from column A as the result of the formula

In this case the result i am looking for would be "2946".

Example:
A B C
2946 Learning Door Project 10.5138
2946 Hardware 0
2946 Software 0
2946 Internal Salaries 0
2946 Consultants 10.5138
2946 Consultants C Tfr from Opex 0
2946 Consultants T Tfr from Opex 0
2946 Intercos 0
2946 Other 0
2953 Q3 Q4 2012 Relations -1.2375
2953 Hardware 0
2953 Software 0
2953 Internal Salaries 0
2953 Consultants 0
2953 Consultants C Tfr from Opex 0
2953 Consultants T Tfr from Opex -1.2375
2953 Intercos 0
2953 Other 0
2965 FE + SE Maintenance 1.6418
2965 Hardware 0
2965 Software 0
2965 Internal Salaries 0
2965 Consultants 1.6418
2965 Consultants C Tfr from Opex 0
2965 Consultants T Tfr from Opex 0
2965 Intercos 0
2965 Other 0

Any help would be GREATLY appreciated! My spreadsheet has over a thousand projects...
Thanks in advance for your inputs!
CT

[tigeravatar]

CMTR,

Welcome to the forum!
Using your sample data, the formula should look like this:

=INDEX(A2:A28,MATCH(MAX(INDEX((B2:B28="Consultants")*C2:C28,)),C2:C28,0))

[Wagstaff]

Try this

=IF(INDEX($A$2:$A$26,MATCH("Consultants*",$B$2:$B$26,0)),LARGE(C2:C26,1))

The astrix is a wildcard as I wasn't sure if you test for Consultants C Tfr from Opex
etc..

Regards

[CMTR]

Thanks Tigeravatar and Wagstaff! Much appreciated

@Tigeravatar: your formula gave me the right project number (2946) but i am not sure of how i can modify it to find the top 3 highest projects? Shouldn't there be a LARGE somewhere so i could do a LARGE(C2:C26,1), LARGE(C2:C26,2) and LARGE(C2:C26,3) in 3 different cells?

@Wagstaff: your formula worked but did not yield the project number as i was looking to get, it provided me with the highest value associated to the "Consultants" costs...

Again, thanks for your inputs. Already helped me tons!
CT

[tigeravatar]

CMTR,

Just change the MAX for LARGE, like so (the red 1 what should change to 2 or 3):

=INDEX($A$2:$A$28,MATCH(LARGE(INDEX(($B$2:$B$28="Consultants")*$C$2:$C$28,),1),$C$2:$C$28,0))

[CMTR]

This works perfectly... thank you SO much! Saved me hours and hours for sure on this