INDEX MATCH Using two different criteria one is dependent on the other for it's result

[GYoung63]

I set up a spreadsheet to track all of the projects we bid on in the last year. At the bottom I summarized each column to show how many of each category we bid on. Below that I used a COUNTIF function to show how many of each category we actually won. Below that I used a simple INDEX MATCH function to select which category we bid on the most and then return the corresponding number of times we won projects in that particular category. This formula works great except when we have two categories with the same maximum number of pursuits. In this case we pursued 23 projects and we pursued 8 of each "Single Family Houses" and "Doghouses". (Obviously this is fictitious data). We won 1 of the houses and 3 of the doghouses but my INDEX MATCH formula only selects the first one and returns the "1" which corresponds to the Houses. I need to be able to first select the maximum number of pursuits from the various categories (8 in this case) and then select the maximum number of wins from those. In most cases there will only be one category with the maximum number, but as evidenced here it is very likely to have two columns matching and it is also possible I guess to have a situation where three or more columns match. (Note I've deleted all of the rows with the actual data from this example otherwise Rows 3 through 16 would be about 20 higher)

The formula in Cell C13 is =INDEX(C$3:F$3,MATCH(C14,C$4:F$4,0))
The formula in Cell C15 is '=INDEX(C$7:F$7,MATCH(C14,C$4:F$4,0))

1/A B C D E F G H I
2 DELIVERY TYPE RESULTS
3 PROJECTS Airports Houses Doghouses Nuke Plants WON LOST DROPPED
4 SUBTOTALS 7 8 8 0 5 15 3
5 30% 35% 35% 0% 22% 65% 13%
6 TOTAL 23 23
7 WON 1 1 3 0 5 0 0
8 20% 20% 60% 0%
9
10 Total # Projects Bid 23
11 # Projects Won 5
12
13 Most Frequent Type Houses <=={This is the one I need to select the second 8 and return "Doghouses"
14 # Pursued 8 Pursued
15 # Won 1 Won <=={This needs to return 3 based on the second instance of 8 above}
16 Win Ratio 13% 1: 8

 

[Rijnsent]

Hi GYoung,
what you could do: get the MAX() formula to find the max value, after that use a dependent list: Create dependent drop down lists containing unique distinct values in excel | Get Digital Help - Microsoft Excel resource to populate your second dropdown. The formulas are rather complex, but it can be done.
Hope that pointer helps a bit,
Koen