Formula to search a range of cells and then compare dates

[shakeregg]

Hey all,

Hopefully this makes sense!

I've got a long list of names in Column E (first name in E4) and I want to search Column AF which contains a different set of names. If there is a match I would like to compare the contents (dates) of two other cells (G and X). If the date in X is equal to or less than the date in G return a 'Yes'.

eg.......... Compare E4 with AF:AF............ There is a MATCH in AF22 so G4 is compared with X22.
Compare E8 with AF:AF............ There is a MATCH in AF6 so G8 is compared with X6 etc etc.

Cheers in advance!

 

[shakeregg]

Oooh! I have a formula in AF which changes the format of the name from Joe Smith to Smith, Joe (the same format as E) .......... is it that that's the problem??

 

[Fluff]

What's the formula?

 

[shakeregg]

It’s =IF(V4,””,””,CONCENTRATE(AC4,”,”,AB4))

 

[Fluff]

What is Concentrate?
Also the formula is adding a comma between the words, but you showed it as Smith, Joe
ie comma space

 

[shakeregg]

I think I got the formula from an old post from here and it combines two columns together. AC Column is the surname and AB is the first name. In AF it comes out as Surname, Firstname which is the same as format as E

 

[Fluff]

In an unused cell type
=E4=AF26
where AF26 is the cell that matches E4, what does it say?
Also try
=len(e4)
and
=len(af26)
what do those return?

 

[shakeregg]

Just tried it again and it worked!! Thank you all three for your help.............. I really appreciate it! Apologies though for prolonging the thread!

 

[Fluff]

Glad it's sorted & thanks for the feedback