VLOOKUP to return data from multiple columns

Hey all,

I am trying to find some sort of VLOOKUP or INDEX formula that will do the following:

The spreadsheet has two tabs, one summary and one that will calculate monthly IRR's. I'll try to attach a simple example.
Tab 1: addresses going down column A. Column B has purchase price, C has sale price, D has purchase date, E has sale date.
Tab 2: addresses going across row 1 with months going down column A.

What I would like is for tab 2 to be able to find the 1st address, go to tab 1 and find that address and find the purchase price (a simple VLOOKUP) but ALSO find the corresponding purchase date, and then in tab 2, place the purchase price in the correct monthly cell. And then do the same for sale price and date. I did 1 Main St manually so you can see what the end result will be. Hopefully I'm explaining this correctly. The reason it needs to be broken into two tabs (I'm pretty sure) is because I need to get a total cash flow by month across the entire 500 property portfolio.

Thanks!

Put this in B2 of Sheet 2 and copy across and down as needed.

Please Login or Register  to view this content.

Does that work for what you need?

oh wow. That worked perfect! Just for educational sake, you mind breaking that formula down as to what it's actually doing?

Sure. It is using two SUMIFS formulas and adding them together. The first sumifs formula is looking for purchase price and will sumif Address on Row 1 (from Sheet 2) matches address in Column A (on Sheet 1) and if date in Column A (of Sheet 2) matches date in Column D (of Sheet 1). The second SUMIFS is picking up the Sale Price in a similar fashion. In an instance where the Purchase and Sale happen on the same date for the same address, it would net them together. In most instances, where the purchase and sales prices for the same address happen on different dates it will add purchase price and $0 (because there is no sales price on that date), or sales price and $0 (because there is no purchase price on that date).

Hope that is a little clearer.