Get the column to the right of a Sumproduct & Max formula

Ok I have a formula that checks a table of data and checks to make sure the animal matches and that the date associated to that animal is less than a specified date. The formula is as follows:

Please Login or Register  to view this content.

and this brings back the date column info and this works fine. However I now need to bring back the info on the column to the right of it called 'Number Key' and I am struggling to get it going. So the formula should bring back 3 as that row is DOG and its date is just less than the 19/03/13. I have tried the below formula:

Please Login or Register  to view this content.

and although it looks like it's working it doesn't work for different test scenarios like if I choose BIRD and the date 18/01/2013 it gets the wrong Number Key. Does anyone have any ideas of how I can extend my original formula to get the column to the right, or how I can modify the array formula to bring back the right info? I have attached the spread sheet to show you what I mean. Your help is much appreciated!

bringing back the column to the right.xlsx

Thanks so much FDibbins and AlKey for your responses! I had no idea you could simply put an & to concatenate both the lookup value and the search array. This will come in so handy - thanks once again, it is much appreciated!

Hey Guys,

I just realised that the formulas you gave me don't work. Your formulas only bring back matching dates, but I need this formula to bring back a date less than the date in F3.

None of your formulas do this and instead you changed the reference date to E7 so that way you could find a matching date. So to clarify the formula needs to match the animal in E3 and the date should be less than F7 and it should bring back the result in column C. Do you have any ideas? Thanks,

Ahhr don't worry, I figured it out using your techniques, thanks once again!

Please Login or Register  to view this content.

Hi Tony,

Thanks for your reply. Unfortunately though your formula doesn't check if the date is less than F7. You have done the same thing as everyone else for some reason. Do you know how to make your formula work so it solves the 2 criteria?

Thanks,

Thanks guys, yeah it works great. I didn't know you guys were referencing E7. I thought you were trying to say that the formula was 'all inclusive' so to speak. Because what I need to do is have just 1 formula to do this, I wont be able to split it up into 2 calculations on my project. But this was a silly mistake on my behalf. Thanks once again all for your help.