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!

 

[AlanY]

put this in row 4 and copy down, adjust range to suit

=IF(INDEX(X:X,MATCH(E4,$F$1:$AF$A15,0))<=G4,"Yes","No")

 

[shakeregg]

Cheers AlanY............ appreciate it

Still new to this so when you say adjust range to suit.......... assuming it's $F$1:$AF$A15. I'm wanting to match a cell in E with AF:AF so would this just be $AF$:$AF$?

Cheers

 

[KolGuyXcel]

Cheers AlanY............ appreciate it

Still new to this so when you say adjust range to suit.......... assuming it's $F$1:$AF$A15. I'm wanting to match a cell in E with AF:AF so would this just be $AF$:$AF$?

Cheers

I guess a small correction is needed in the formula suggested by AlanY in post#2
=IF(INDEX(X:X,MATCH(E4,$F$1:$AF$A15,0))<=G4,"Yes","No").

It should be
=IF(INDEX(X:X,MATCH(E4,$AF$1:$AF$15,0))<=G4,"Yes","No")
i.e. with an A immediately after the first $ sign and also without the A in between $AF$ and 15

Adjusting the range would mean to change the row numbers in "$AF$1:$AF$15". I'm thinking could possibly be "$AF$4:$AF$22" or more!


HTH!

 

[shakeregg]

Aaaah okey doke......cheers!

Just for clarification.............. will the above formula identify a specific cell in Column X?

eg.......... I want to Compare a name in E4 with names in Column AF............ Say there is an exact MATCH in AF22. As it's AF22 I then want to then compare X22 and only X22 with G4 to see if they match. If the exact match was AF46 I would instead want to compare X46 with G4.

 

[Fluff]

Just for clarification.............. will the above formula identify a specific cell in Column X?

Best way to find out is, try it.

 

[shakeregg]

Ha true!

Just tried and it's coming up with a #N/A when I input the formula......... scratch of the head moment. Any further help would be really appreciated!

 

[Fluff]

Ok, lets break it down, what do you get if you use
=INDEX(X1:X15,MATCH(E4,AF1:AF15,0))
change the X15 & AF15 to match the last row of data

 

[shakeregg]

Just checked and it comes up with #N/A

 

[Fluff]

In that case it can't find an exact match for E4 in col AF
What sort of values do you have?