Look up and extract information in another column

[hgrego]

Hi Community

Below is a sample of my data set. I'm trying to extract the information into new columns (in red font) . Basically I want to check if they travelled to the country and to return their date of travel. Any help is appreciated!

Country	Travel log	Travel date	Extract country	Extract date
United States	Hong Kong, United States	2021-06-07, 2021-10-04	United States	2021-10-4
Brazil	Brazil, China, Australia	2022-07-12, 2022-06-02, 2022-05-09	Brazil	2022-07-12
Canada	United Kingdom	2021-11-11, 2021-12-12	No record	No record

[AngelosPachis]

Hi @hgrego ,

Here's one way you can do it by splitting the different travel logs per country into rows and checking whether the records contained there matches the values in the country field. If the second record matches the value, then the second date will be picked up by using the GetWord function.

Hope that helps,

Angelos

[flying008]

Hi, @hgrego 

Maybe this is your want :

1- Formula of  [ Extract_date ] :

Trim(IIF([Country]!=Trim([Travel log]),Null(),GetWord([Travel date],  [Tile_SequenceNum]-1)),",")

******

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

[flying008]

Hi, @hgrego 

And there is a easy way with formula today.

******

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.