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

#### GYoung63

##### New Member
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

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Replies
5
Views
160
Replies
4
Views
269
Replies
3
Views
194
Replies
5
Views
255
Replies
1
Views
163

1,190,677
Messages
5,982,218
Members
439,769
Latest member
trungminh2802

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back