# combining countif and match

#### kcmuppet

##### Active Member
In the example below, is there a simple way to get column B values to be populated with the highest value from column C which has the same value in column A.?

So for example the formula in B5 would go something like...
if the value in column A already is not the first in column A, then for all the rows that have the same value ("JS" in this case), find the highest value (most recent date) in column C and put it in here.

I've been struggling with countif & match, but not really getting very far. Can anybody help?
Rocksharp Books 05-06 v1-60 -reimbursements.xls
ABCD
2EmployeeLastreimbursedReimburseto(date)AmountDue
3KC01-Apr-0431-Mar-053,092.47
4JS08-Apr-0428-Feb-05
5JS28-Feb-05
Reimbursements

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### NBVC

##### Well-known Member
I wasn't sure if you wanted the date of the last reimbursed date for everyone as a group or specific to that individual, but here are 2 solutions. Apply the one that best fits your needs.

The solution in the green area assumes you want the last reimbursed date for all as a group.

The solution in the blue area assumes you want to get the last reimbursed date for that individual.

Not sure what date you want for an employee not already in the list so I just returned a blank (""). You can replace that with the cell reference that contains the date you want to be populated, or if todays day, replace with Today().

Do not, however, overwrite the formula or it will be lost from that cell.

Formula in B2 is =IF(\$A1="Employee","",IF(COUNTIF(\$A1:\$A\$2,\$A2)>0,LOOKUP(9.99999999999999E+307,\$C1:\$C\$2),""))

Formula in F2 is =IF(\$E1="Employee","",IF(COUNTIF(\$E1:\$E\$2,\$E2)>0,SUMPRODUCT(MAX((\$E1:\$E\$2=E2)*\$G1:\$G\$2)),""))

Whichever, you choose, adjust to suit your column lettes and row numbers.

Then copy down the formula to bottom of list.
Book2
ABCDEFG
1EmployeeLast ReimbursedReimburse to (date)EmployeeLast ReimbursedReimburse to (date)
2KC 31-Mar-05KC 31-Mar-05
3js 28-Feb-05JS 28-Feb-05
4JS28-Feb-05JS28-Feb-05
5JS28-Feb-05JS28-Feb-05
6JS28-Feb-05JS28-Feb-05
7JS28-Feb-05JS28-Feb-05
8JS28-Feb-05JS28-Feb-05
9JS28-Feb-05JS28-Feb-05
10JS28-Feb-05JS28-Feb-05
11KC28-Feb-05KC31-Mar-05
12NC NC
Sheet2

#### kcmuppet

##### Active Member
NBVC said:
I wasn't sure if you wanted the date of the last reimbursed date for everyone as a group or specific to that individual
For the specific individual, thankyou - that's just was I was looking for.

Not sure what date you want for an employee not already in the list so I just returned a blank (""). You can replace that with the cell reference that contains the date you want to be populated, or if todays day, replace with Today(). Do not, however, overwrite the formula or it will be lost from that cell.
That date will need to be entered manually I think, for that cell. It seems it doesn't matter if the formula is overwritten for just that cell because the it will still exist in the cells below, right?

Out of interest, in the first solution (as if it were for all employees), could you explain if you have time how the lookup works. I don't understand the siginficance of that big value.

Thanks for your help with this.

#### NBVC

##### Well-known Member
That's the largest number Excel can handle, so the lookup looks for that big number in your table and when it doesn't find it, it returns the closest match (ie. the largest number) to that without going over (which should never happen).

Here is a link that explains it well.

http://www.mrexcel.com/board2/viewtopic.php?t=105725

Replies
5
Views
126
Replies
14
Views
652
Replies
6
Views
207
Replies
6
Views
375
Replies
9
Views
228

1,195,624
Messages
6,010,750
Members
441,568
Latest member
abbyabby

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