# Index Match Count

#### philb99

##### Active Member
Hi - I had some great code from Mrmmickle1 but I would like further code please

I have put my Drop Box Link

https://www.dropbox.com/s/prmdf2unq4fy5je/dropbox2.xlsx?dl=0

Col E tells me if a case has been received

I have created in Col G, with coded help, all the Case Numbers where No Case - What I would like please in Col J details the Customer Number from Col B Linked directly to the No Case Number in Col G.

The I can report No Case and Customer Number

My spreadsheet could run to 20000+ lines

Thanks

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### FDibbins

##### Well-known Member
I am not able (allowed) to access file-hosting sites, so could you paste some sample data here please?

From what I can make out, take a look at using the countifS() function

#### philb99

##### Active Member
Thanks for the reply - should be able to Click Open after clicking the link - are you saying that doesn't work?

#### FDibbins

##### Well-known Member
I am not allowed to access file-hosting sites

#### philb99

##### Active Member
ok Here is the code I use in Col G =
 INDEX(\$A\$2:\$A\$100, MATCH(0, IF("No Case"=\$E\$2:\$E\$100, COUNTIF(\$G\$1:\$G1, \$A\$2:\$A\$100), ""), 0))

<tbody>
</tbody><colgroup><col></colgroup>

##### MrExcel MVP
ok Here is the code I use in Col G =
 INDEX(\$A\$2:\$A\$100, MATCH(0, IF("No Case"=\$E\$2:\$E\$100, COUNTIF(\$G\$1:\$G1, \$A\$2:\$A\$100), ""), 0))

<tbody>
</tbody>

In G1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(\$A\$2:\$A\$12),IF(\$E\$2:\$E\$12="no case",MATCH(\$A\$2:\$A\$12,\$A\$2:\$A\$12,0))),ROW(\$A\$2:\$A\$12)-ROW(\$A\$2)+1),1))

In G2 control+shift+enter, not just enter, and copy down:

=IF(ROWS(\$G\$2:G2)>\$G\$1,"",INDEX(\$A\$2:\$A\$100,SMALL(IF(FREQUENCY(IF(ISNUMBER(\$A\$2:\$A\$12),IF(\$E\$2:\$E\$12="no case",MATCH(\$A\$2:\$A\$12,\$A\$2:\$A\$12,0))),ROW(\$A\$2:\$A\$12)-ROW(\$A\$2)+1),ROW(\$A\$2:\$A\$12)-ROW(\$A\$2)+1),ROWS(\$G\$2:G2))))

#### thisoldman

##### Well-known Member
Your INDEX formula in column G is an array formula. When an IF function contains an array operation, Ctrl+Shift+Enter must be used.

Excel 2012
ABCDEFGH
1case numbercustomer numberRegionMatched Casecase Y/NNo Case -Numbers
21123Nn/aNo Case1
550No Customer NumberEn/aNo Case#N/A
6
Sheet2 (2)
Cell Formulas
RangeFormula
G2{=INDEX(\$A\$2:\$A\$92, MATCH(0, IF("No Case"=\$E\$2:\$E\$92, COUNTIF(\$G\$1:\$G1, \$A\$2:\$A\$92), ""), 0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Last edited:

#### philb99

##### Active Member
Thanks for the reply thisoldman - I have the array already (You appear to have just copied my existing formulae) what I am looking for is in Col J please

#### philb99

##### Active Member
Thanks AA - long time. Not sure what it is you are trying to tell me - how you looked at my dropbox - I am after the results in Col J - All of my other formulae I am ok with

##### MrExcel MVP
Thanks AA - long time. Not sure what it is you are trying to tell me - how you looked at my dropbox - I am after the results in Col J - All of my other formulae I am ok with

In J2 enter and copy down:

=IF(ISNUMBER(\$G2),VLOOKUP(\$G2,\$A\$2:\$B\$12,2,0),"")

Replies
0
Views
311
Replies
0
Views
1K
Replies
2
Views
259
Replies
3
Views
396
Replies
5
Views
383

1,190,916
Messages
5,983,567
Members
439,849
Latest member
Tlaw

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