finding prior "record" within sumproduct

[DEllis81]

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!!
​

 

[fairwinds]

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.

 

[DEllis81]

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!

 

[fairwinds]

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.