Multiple criteria lookup including recent date.

**Dimydom** · 09-29-2012, 06:06 AM

Hi I'm sorry if this seems redundant to you all, but I can't seem to locate a solution that could solve my problem when I searched the forum. But I did find some formulas and functions but I just could not make them work. I have attached a copy of my excel file.

I would like to lookup the most recent "MarketPrice" from the PortfolioTransaction Sheet. I will have 3 criteria, Recent date, Code, Type. I tried using the following formula that I found from the net and was suggested often, but they did not have the most recent date criteria in them so I tried adding it myself. Here it is.

{=INDEX(PortfolioTransaction!E:E,MATCH(1,(PortfolioTransaction!B:B=PortfolioSUM!A2)*(PortfolioTransaction!D:D="Buy")*(MAX(PortfolioTransaction!A:A)),0))}

But this returns a #N/A result.

I've been tweaking this for hours now, my head is starting to hurt.

Thanks!

**benishiryo** · 09-29-2012, 06:28 AM

hi Dimydom, welcome to the forum. try this array formula:

Please Login or Register  to view this content.

as you have done with your formulas, CTRL + SHIFT + ENTER to confirm. i do not recommend looking up the whole column as it slows down Excel. more so if you are using array formulas.

**Dimydom** · 09-29-2012, 06:38 AM

Thank you benishiryo, I'll try it now. Regarding the whole column, yes, my file does seem to slow down excel to the point it sometimes crashes, I thought I got a virus from somewhere.

Thanks! Will tell you what happens.

**Dimydom** · 09-29-2012, 06:43 AM

It worked!

I will now deconstruct what you gave me so that I will be able to understand how you did it... My head is still hurting, but you made it more bearable!

Thanks!