Join two fields, one with text format and the other with a date (which is a result of formula)

NJCunliffe

Hi,

Help please.

I have a name in one cell [Service User] and a date in another [Visit Date] and wish to join them together to use as a lookup primary key value.

The date is extracted from a date/time using a formula which is working fine. However, using the + or join formula does not bring the date half of the concatenation back so the lookup finds no match. How can I get the date to behave as a text when it is extracted from the result of a formula please?

=JOIN([Service User]@row + [Visit Date]@row)

Ryan Sides

Hi @NJCunliffe , which part isn't working? The LOOKUP or the JOIN?

NJCunliffe

The join is the issue.

NJCunliffe

After the join, I am trying to use in a new lookup which give no match.

Ryan Sides

@NJCunliffe is it not matching on any of the joins?

If this is the case, check your data on your lookup sheet (not the one pictured) to make sure the data is exactly the same. As a test, you can copy the result of one cell of the join, then go to your lookup sheet and "Paste Special , Values" to see if it brings back that row.

NJCunliffe

Hi Ryan,

Some good ideas there thanks. I concatenated the cells I needed and did paste special in each sheet to get rid of formulas. Using the sample "Betty Coe09/12/22", I did find and replace and found the sample in both sheets.

I thought great, this gets me going. The lookup again found #NO MATCH :-(

Any other ideas? My client is really not happy that I did not fix this yet so feeling the pressure. Appreciate your assistance.

Screenshot 2023-01-19 at 14.51.02.png

Ryan Sides

@NJCunliffe do you want to jump on a zoom call so you can share your screen? Let's get this tied up for you. Send me an email when you're ready. I'm happy to do it. Ryan@WorkflowCreative.com

NJCunliffe

I think that this is proof that it is a formula issue as when I paste values and use this in the lookup, the comments are found [Column 31].

=[SLA Visit Date]@row + [Call Type]@row + [Service User]@row

Both are date fields although not pure date obviously.

Can anyone help with this please?