Hi Forum members,

Excel 2007.

In order to retrieve the most recent occurance of an incident for an application instance number, I have the following array formula that works fine:
Please Login or Register  to view this content.
What this does is looks up the existence of an instance ID in a named range of a database in columns on Sheet1 and if certain criteria are met returns the latest date recorded for that instance (result on the same row in column E on Sheet2 next to the matching instance ID in column D).
All is fine as long as instance ids in the named range on Sheet1 have a single id in the cell.
However, there are scenarios where there can be 2 or more numerical ids in the same cell seperated by semicolon in the database (named range on sheet1)

numerical ids can consist of a single digit up to 5 digits.
The list of ids on Sheet2 column D always have a single id

Is there a way (without adding more columns to the data base on Sheet1) to extract each instance id from the cell and on sheet2 column E have the same max date returning for each of the applicable instance in that single cell on Sheet1?

eg. Sheet1!A3 7865;6241;25988;3 Sheet1!A4 24-Jun-2013

Desired result:
Sheet2
D8 3 E8 24-Jun-2013
D9 6241 E9 24-Jun-2013
D10 7865 E10 24-Jun-2013
D11 22171 E11 01-Oct-2012
D12 24603 E12 -
D13 25988 E13 24-Jun-2013
D14 79421 E14 -

To make matters more interesting, I have a similar setup with text values in another named range, using a similar MAX formula.
Also here there could be a possibility of 2 or more text values in the same cell.

Your help would be greatly appreciated.

Many thanks,

Mark