Look up Latest Dates from a Table Column based on substring contained in another column

[daninikunj]

Hi,

I could use some help with looking up latest date from an Excel Table based on one of its column containing specific substrings. Refer to the attached Excel file for example and the screenshot below.

I have three tables in the attached Excel File.

Table1 - Two Columns - City & Date
Table2 - Single Column - City Choices
Table3 - Latest Date Pulled using Calculation


I am trying to pull latest date from a Table1, Column2 (Date) based on value in its first column (City) containing the substring in Table 2 (City Choices)

Ex. Since I have NYC and PHL included in Table 2, my latest date is 6-Jul-2019. If I were to remove PHL, then the latest date should change to 5-May-2019.

I'd appreciate the help, Let me know if there are any questions. I made multiple attempts but having my data in a table is making it difficult for me.

LookupBasedOnSubstring.jpg

[Bo_Ry]

Please try at D2
=MAX(Table1[Date]*ISNUMBER(FIND(TRANSPOSE(Table2[City Choices]),Table1[City]))*TRANSPOSE(Table2[City Choices]>0))

Press Ctrl+Shift+Enter

[daninikunj]

Thanks Bo_Ry, thanks for your quick reply. This works great. But I noticed that it didn't work in my actual application because the Date column in my application is a calculation and sometimes the calculation returns a value that is NOT a date and is a text instead and because of that, I get get a #Value! in D2. Is there a way to filter out values other than Date?

I have attached a revised file to indicate the issue. Sorry, I didn't think of providing this information earlier..

Thanks in advance,

Nick.

[Bo_Ry]

Please try at D2
=MAX(N(+Table1[Date])*ISNUMBER(FIND(TRANSPOSE(Table2[City Choices]),Table1[City]))*TRANSPOSE(Table2[City Choices]>0))

Press Ctrl+Shift+Enter

[daninikunj]

Hi Bo_Ry.. The revised formula worked. Thanks so much. Appreciate your help and quick response!