# finding prior "record" within sumproduct

#### DEllis81

##### Board Regular
I am looking for a formula that pulls a value from the most recent record (date) in a continous datalog. I have a series of contracts within column B, and every few days - new data is entered for numerous contracts. I am wanting to pull a value (column E) from the most recent entry, given same contract ID (column B). Column A is my date.

for example for a date 10/1 column E has a value of 100
and then on date 10/8, I am wanting to display the 100 in a unique column. But between the two records - are multiple other contracts.

Below was my initial attempt - however the max formula is evaluating to a date value vs a desired true/false.

=SUMPRODUCT((B42=B\$4:B41)*(MAX(A\$4:A41))*(E\$4:E41))

Again, thanks for the wonderful help I have aquired thru this site!!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi,

Try:

=VLOOKUP(MAX(IF(B42=B\$4:B41,A\$4:A41)),A\$4:E41,5,0)

This formula needs to be confirmed with Ctrl + Shift + Enter.

THank you... I was unable to get a "array" formula enterd for a vlookup - but got something similar for a sumproduct. Back to my Ctrl/shif/enter - not sure why that wouldn't work for me - use it all the time. Are all excel functions capbale of array formulas?

thanks again!

Hi,

No not all formulas. Actually VLOOKUP does not however in this example the VLOOKUP is not done on an array. Only on a single value from MAX.

The important thing to understand is if the output from a formula is an array or a single value.

Replies
6
Views
333
Replies
15
Views
373
Replies
7
Views
294
Replies
7
Views
356
Replies
1
Views
342

1,202,915
Messages
6,052,535
Members
444,590
Latest member
GCLee

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