One issue you may find with dates when extracting from a look up table is that an empty date field generates a zero. You can avoid that with a simple formula adjustment.
In the image below we have a list of customers and a transfer date. Not all customers have a transfer date.
In the sales table below we need to bring in the transfer date from the lookup table. The problem is a standard XLOOKUP will return zero for the empty cells – see the entries 0/0/1900 below.
The solution is the LET function below.
The captures the XLOOKUP result in the x variable. It then tests x to see if it equals a blank. It then displays a blank cell instead of a zero.
You could use just an IF function but repeating the XLOOKUP function makes the formula a lot longer – see below.
Another formula that would work…
=IFERROR(XLOOKUP(B2:B10,Table!A2:A10,0+LEFT(Table!B2:B10,5)),””)
Thanks Rick
Actually, I missed a pretty simple one…
=XLOOKUP(B2:B10,Table!A2:A10,IF(Table!B2:B10,Table!B2:B10,””))
Thanks again Rick
Note sure about simple – many people don’t know that the IF function can accept values for the logical test.