Select Newest date related a value

[secoo140]

I want to select newest PET date. But I can not figure it how.


IF B:B is PET
then select newest date?

 

[Fluff]

How about

Excel 2013/2016
	A	B	C
1	13/10/2017	SPECT/BT	15/10/2018
2	01/02/2018	SPECT/BT
3	11/03/2018	PET-Ga68
4	11/04/2018	SPECT/BT
5	12/05/2018	SPECT/BT
6	13/06/2018	PET-Ga68
7	13/07/2018	PET-Ga69
8	14/10/2018	PET-Ga70
9	15/10/2018	PET-Ga71
10	15/10/2018	SPECT/BT
Sheet2

Cell Formulas
Range	Formula
C1	{=MAX(IF(LEFT(B1:B10,3)="PET",A1:A10))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[secoo140]

I use excel 2010
and this formula doesnt work.

LEFT(B1:B10,3)="PET"

it says error B10,3

 

[Marcelo Branco]

I use excel 2010
and this formula doesnt work.

LEFT(B1:B10,3)="PET"

it says error B10,3

Does your Excel version use semicolon (;) as argument separator?
If so, try
=MAX(IF(LEFT(B1:B10;3)="PET";A1:A10))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[secoo140]

IT WORKS!
thank you so much!

ctrl shift enter was crucial. my fault

Does your Excel version use semicolon (;) as argument separator?
If so, try
=MAX(IF(LEFT(B1:B10;3)="PET";A1:A10))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[steve the fish]

It looks like date order so could go with:

=INDEX($A$1:$A$1000,MATCH("PET*",$B$1:$B$1000,0))

 

[Marcelo Branco]

It looks like date order so could go with:

=INDEX($A$1:$A$1000,MATCH("PET*",$B$1:$B$1000,0))

Steve,

Your formula returns the first match, not the last.
Assuming the dates in ascending order maybe...
=LOOKUP(2,1/(LEFT(B1:B1000,3)="PET"),A1:A1000)

M.

 

[steve the fish]

Steve,

Your formula returns the first match, not the last.
Assuming the dates in ascending order maybe...
=LOOKUP(2,1/(LEFT(B1:B1000,3)="PET"),A1:A1000)

M.

Oh yes i didnt read properly.