Formula to Update Dates

[uk747]

Hi i have the data below and another set of data with updated Dates in F4:G6

What I need is to check if the Names below in B2:B11 appear in F4:F6.
If they do enter the new date and if they don't enter the existing Date

e.g. Adam isn't in 2nd list so would be 03/07/1988, Ben appears in 2nd list so would be 12/08/1996 and Carl who doesn't appear would be Blank as his original date is Blank

This formula would appear in Column J and then i can overwrite the original dates in Column C with results of formula
I did an xlookup but got 0s where there were blanks. Added &"" and Dates were formatted as text even used =let to get ""

One thing I noticed was when i copied across they weren't proper blanks e.g when you highlighted all the date column the count was 10 when it should be 7
Only way to get 7 was manually go into each cell that looks like a blank and press delete key

Name	Date
Adam	03/07/1988​
Ben	10/05/1985​
Carl
Dave	10/04/1985​
Emily	29/09/1989​
Freda	13/10/1986​
Greg	05/07/1985​
Harry
Isla
Jane	08/01/1990​

Updated Data is

Ben	12/08/1986​
Emily	14/06/1988​
Greg	05/10/1994​

 

[Snakehips]

@uk747 Does this help?

AutoTraining.xlsm
	B	C	D	E	F	G	H	I	J
2	Name	Date
3	Adam	7/3/88							7/3/88
4	Ben	5/10/85			Ben	12/08/1986			12/08/1986
5	Carl				Emily	14/06/1988
6	Dave	4/10/85			Greg	05/10/1994			4/10/85
7	Emily	9/29/89							14/06/1988
8	Freda	10/13/86							10/13/86
9	Greg	7/5/85							05/10/1994
10	Harry
11	Isla
12	Jane	7/8/85							7/8/85
Sheet7

Cell Formulas
Range	Formula
J3:J12	J3	=IFERROR(XLOOKUP(B3:B12,F$4:F$6,G$4:G$6,,,),IF(C3:C12>0,C3:C12,""))
Dynamic array formulas.

 

[uk747]

Hi Thanks but it still gives me the same as I've already got but yours is in an array.

I still get the count of 10 when pasting over original values
e.g. Copy J3:G12 and Paste Special as Values in C3. When you highlight C3:C12 at bottom Count says 10 whereas before it was 7

 

[Snakehips]

@uk747 Ok, I get it.
This is down to the long-standing anomaly of the 'Absolute Blank' vs Formula Resultant "" when using COUNTA()

Can you explain how the count of 10 rather than 7 impacts badly on your data / result?

 

[uk747]

Hi

Not sure, its just something I noticed when counting them and wanted to remove if possible.

Wasn't aware of an anomaly with Absolute Blanks so thanks for letting me know. It wasn't a big issue was just confused when I saw result and wanted to avoid possible issues maybe further along the line when analysing the data

For now I'll accept your solution as was neater than mine