Find Largest Number In Row Whose Adjacent Cell Meets Criteria

[jimmycooker]

Hi,

I have rows where time stamps are entered sequentially / chronologically that include 3 fields:
Name1, Department1, TimeStamp1, Name2, Department2, TimeStamp2, Name3, Department3, TimeStamp3, etc etc.

I need to find the highest date (number) based on matching adjacent department criteria (in the columns to the left of the time stamps). Can anyone help?

I'm using index match, but it always returns the 1st value (the lowest).

Thanks.

 

[alansidman]

Upload some sample data so that we can manipulate it and not have to visualize what you see. Please use the XL2BB function available on this forum. See my signature for instructions if you are unfamiliar.

 

[jimmycooker]

Signature 1

Department 1

Date 1

Signature 2

Department 2

Date 2

Signature 3

Department 3

Date 3

Signature 4

Department 4

Date 4

Signature 5

Department 5

Date 5

John

CQV

16-May-22​

Jill

MFG

17-May-22​

Eric

QA

18-May-22​

Jacob

CQV

19-May-22​

Jonnice

CQV

20-May-22​

Search lower row for latest date that corresponds with departement 'CQV'

 

[jimmycooker]

Can't isntall the add in on my work laptop sorry.....

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jimmycooker]

365 (profile updated). It's a work laptop, so installations restricted....

 

[Fluff]

Thanks for that.
How about

=MAXIFS(C2:O2,B2:N2,"CQV")

 

[jimmycooker]

Wow i wasted hours last night trying to achieve the same thing, Thanks so much.

 

[jimmycooker]

How about if i wanted to pull the corresponding name to the max date and department?

 

[Fluff]

How about

=INDEX(A2:M2,MAX(IF(B2:N2="CQV",COLUMN(A2:M2),)))