Extracting date from different text values

[JPSExcel]

Hello everyone,

I need a formula that can identify different text values (three countries) and match them with a date.

In the attachment, I need the formula to search for the text value "Dominican Republic" or "Jamaica" or "Bahamas" in the range and match the earliest date.

I explained what the formula is supposed to do in the excel file.

Thanks a lot for your help.

[alansidman]

A Power Query Solution:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", Int64.Type}, {"Season", type any}, {"Date", type date}, {"Location", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Season"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Location] = "Jamaica" or [Location] = "Bahamas" or [Location] = "Dominican Republic"),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Location"}, {{"Earliest", each List.Min([Date]), type nullable date}})
in
    #"Grouped Rows"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[XOR LX]

Hi,

=INDEX(D18:D47,-LOOKUP(0,-MATCH({"Jamaica","Dominican Republic","Bahamas"},E18:E47,0)))

Regards

[JPSExcel]

Hi,

=INDEX(D18:D47,-LOOKUP(0,-MATCH({"Jamaica","Dominican Republic","Bahamas"},E18:E47,0)))

Regards

Thank you so much! This solved my problem.
Have a great day.

[XOR LX]

You're welcome!

Cheers