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:
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
Bookmarks