Get the latest value from a table

[santhoshkunder]

Please help me achieve this.

Material	Month	Price	Latest Price
A	Jan	10
B	Jan	2	2​
C	Jan	4
D	Jan	5	5​
A	Feb	8	8​
C	Mar	22
C	April	19	19​

 

[ETAF]

if the month is a real date - just formatted to show the month
and this may be a bit overcomplicated but try

=IF(COUNTIF($A$2:A2,A2)=MAX(COUNTIF($A$2:$A$8,A2)),INDEX($C$2:$C$8,MATCH(MAXIFS($B$2:$B$8,$A$2:$A$8,A2),$B$2:$B$8,0)),"")

 

[bosco_yip]

Or,

In E2 formula copied down:

=IF(LOOKUP(9^9,C:C/(A:A=A2))=C2,C2,"")

 

[Peter Bartholomew]

Strictly this is the last match rather than the latest

= LET(
    latestPrice, XLOOKUP(Material, Material, Price, "", ,-1),
    IF(latestPrice=Price, latestPrice, "")
  )

 

[santhoshkunder]

Or,

In E2 formula copied down:

=IF(LOOKUP(9^9,C:C/(A:A=A2))=C2,C2,"")

View attachment 83990

Or,

In E2 formula copied down:

=IF(LOOKUP(9^9,C:C/(A:A=A2))=C2,C2,"")

View attachment 83990

What does 9^9 do here?

 

[bosco_yip]

What does 9^9 do here?

9^9 is a lazy form (short form) of Excel Bignum 9.9999999999999999E+307, please see herein details as below link:

9.9999999 .... ?

Hey there I see this 9.99999999999.. to be used now and then in formulas like =IF((Sheet2!D2"")*(Sheet2!D2=$A$1),LOOKUP(9.99999999999999E+307,$B$1:B1)+1,"") What is the function of that. I know it works in those cases I have seen but I would like to learn how to use it and why? Regards...

www.mrexcel.com

Regards