Looking up multiple occurrences of lookup value, returning corresponding data

Hi all,

Please bear with me as I'm having difficulty getting my head around this one, let alone explaining my problem clearly (I'm a novice!)

I have two 'active databases' (separate excel workbooks), the first is an information register where rows of data are always being added, but never removed/deleted.

The second database (I'll call this one 'tracker') will basically be a duplicate of the data in the register but will only include 'active' items (i.e. items that have not been 'closed out' in the register), so there will always be data being added and removed from this workbook. I hope that makes sense.

Essentially what I need to do is lookup multiple occurrences of the lookup value (located in 'tracker') from my register database. The problem is, I will have multiple, varying lookup values and multiple, varying corresponding results...

Hopefully you're still with me at this point!

To elaborate (here's where it will get really messy, haha), what I'm trying to do on the 'tracker' workbook, is set up a column (let's say Column M) next to my data table (which encompasses columns A:L) with formulas that will lookup a value in Column L that may (or may not) occur multiple times, i.e. L7='HELP1', L8='HELP1', L9='HELP1', L10='HELP2', and then return the corresponding data (which is a date) from a column in the register workbook. I managed to use an array formula to achieve this for the first occurrence of the lookup value;

{=INDEX('[Register'!$U:$U,SMALL(IF($L$7='Register'!$O$2:$O$1000000,ROW('Register'!$O$2:$O$1000000)-ROW('Register'!$U$2)+2), ROW('Register'!2:2)))}

I want to be able to just drag this formula down to lookup the value beside it in column 'M', but I know I'm not using the formula correctly, as the lookup value has to be a constant....

This is what I mean, obviously it didn't work;

M7 = {=INDEX('[Register'!$U:$U,SMALL(IF($L$7='Register'!$O$2:$O$1000000,ROW('Register'!$O$2:$O$1000000)-ROW('Register'!$U$2)+2), ROW('Register'!2:2)))}

M8 = {=INDEX('[Register'!$U:$U,SMALL(IF($L$8.....

M9 = {=INDEX('[Register'!$U:$U,SMALL(IF($L$9.....

Is there any way I can achieve what I'm trying to do with VBA? I'm sorry, I can't share my workbooks but I can provide more information if need be.

If it helps to visualise it, this is what the results should look like on the tracker database;

L7 = HELP1, M7 = 02/10/2015 (date returned from register database)
L8 = HELP1, M8 = 15/10/2015
L9 = HELP2, M8 = 20/10/2015

Eternally grateful to anybody who can point me in the right direction, or just let me know if I'm trying to achieve the impossible here.

Cheers!

Perhaps to simplify, what I could say is I'm trying to lookup multiple occurrences based on multiple criteria by having my lookup formula referring to corresponding cells in a table as opposed to having a constant lookup value.....? sorry if this is just as unclear as the rest of my post!

Thank you AlphaFrog!! Thank you very much, that is exactly what I wanted! You are right however, it is extremely inefficient to process...and as the O range/register workbook is a large and ever-expanding range, I can only minimise it so much... I don't mean to be a pain, but can you offer any advice as to whether there is a VBA workaround to speed up the process? Thanks again