Find the oldest date in one cell based on a search for a keyword in other column

[criscocube]

Hi all,

I have a spreadsheet with around 20,000 rows with two relevant columns, date, another cell with "ingredients", the ingredients column can have anywhere from 1 to like 10 items in it, the date cell is in a few formats (sometimes with month/day/year sometimes just year, and in one case a ton of extra serial number **** before the date, i can probably figure out how to remove that in any case).

I need to find the oldest instance of each ingredient (hundreds of unique), regardless of all the other ingredients.

So a sample of what this looks like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]apples, cookies, flour, dead babies, grass[/TD]
[TD]2013-06-24[/TD]
[/TR]
[TR]
[TD]sand, rocks, ice, flour[/TD]
[TD]2000-03-01[/TD]
[/TR]
[TR]
[TD]wood, grass, oranges[/TD]
[TD]1973[/TD]
[/TR]
[TR]
[TD]water, air[/TD]
[TD]1923[/TD]
[/TR]
[TR]
[TD]cookies[/TD]
[TD]A94B324 2014[/TD]
[/TR]
</tbody>[/TABLE]


I feel like im overlooking some simple/obvious function in Excel to do this... or does it require a quick VBA macro?

Thanks!

 

[istiasztalos]

Hi criscocube,

This is an array formula, add with ctrl+shift+enter (Excel with add { } around it if done right), 'ingreds' is your column with ingredient list, 'idates' is the column of dates. Formula assumes that you have already corrected the date format issue and they are valid dates. (if that's not the case it's of course a very different question....)

=MIN(IF(ISNUMBER(SEARCH(D1,ingreds)),idates))


*edit: forgot to mention, D1 is where you type what you want to search for