VLOOKUP not returning correct results

**Lea724** · 07-20-2011, 05:03 PM

Hi,

I'm attaching a spreadsheet and I'm hoping somebody can help me with it.

I'm keeping a lien book that shows how much each home pays in principal and interest (P&I) each year. Based on the type of the house (townhome, duplex, etc.), the original amount of principal is different between the homes and once the property sells, the principal amount that the owner owes changes, with the owner usually paying less than the original amount of P&I and the developer picking up the remainder of the P&I.

Once each home sells, the developer likes to pay its remaining portion of P&I in one lump sum, so the first sheet in the spreadsheet shows the amount of principal paydowns remaining plus six months of interest. It's that six months of interest that's giving me trouble.

If you look at the first example, House 1, on the first sheet, the vlookup formula that I'm using should bring back $191.22 in column I, but instead it's pulling in -$278.15, no clue why. The formula is using vlookup to pull in the sum of the Owner's Portion of Interest plus the Developer's Portion of Interest less the Owner's Portion of Interest.

This is really a spreadsheet you need to look at rather than my explaining it, but I was hoping that somebody would be able to offer a solution to getting my interest due correct.

I did try combining an index formula with a vlookup formula, and while I could get the total interest due to calculate correctly in one cell and the developer's interest due to calculate correctly in another cell, when I tried putting the formulas together, Excel was giving me some crazy number that wasn't correct.

If there's a way to get a match formula to behave similarly to vlookup and look at all of the possible rows (rather than stopping at the first one it matches to), then I believe I could successfully use index and match together, but I don't believe that's possible.

Any help at all is greatly appreciated. Thank you!

Amanda

P.S. As added info, when I was prepping the example spreadsheet for this forum by removing any personal information, I found that deleting subsequent rows that shouldn't have had anything at all to do with the prior homes was affecting my interest due numbers. Something screwy is definitely going on, but heck if I know what.

**Azam Ali** · 07-21-2011, 04:21 AM

Hi Lea724

find the formula in the attatched file

**Lea724** · 07-21-2011, 11:25 AM

Azam,

Thanks so much! I tried the formulas you used and it didn't work. I kept getting a #N/A error with both the "find row" formula and the "nested formula" formula. Because the "find value" formula was reliant on the "find row" formula working, I wasn't able to try that one.

Since it looked like you changed all of the formulas to be hard-coded, I tried doing that as well, but no dice. I've had several people take a look at your formulas along with my inputting them into the original spreadsheet and they all agree that I'm definitely putting them in correctly, but none of us can figure out why Excel isn't behaving.

When I enter in the formulas you came up with through the formula bar, so that Excel can walk me through each step of each formula, the "find row" formula comes up, for your answer to the first one as 11, but blank as my answer, even though the rest of the formulas look identical.

Other than hard coding my information, I don't quite know where to go from here at this point, unless you or somebody else has any ideas.

The file I originally attached was a very small representation of the entire spreadsheet I'm working with. In an effort to try and figure out what might be going on, I'm attaching the full spreadsheet here. I've removed personal information, but you can see that the parcel IDs are listed in it rather than being replaced with "House 1, House 2," etc. as I did with the first spreadsheet. Maybe that has something to do with it?

Any help, as always, is appreciated.

Thanks,

Amanda

**Lea724** · 07-21-2011, 02:21 PM

So it looks like I may have figured out the problem. Azam, your formula *does* work, but I have to make sure that rather than doing searches on values of A:A, on all of the match and index functions, I need to put in the actual row numbers, so, for example, A$10:A923 would replace all of the A:A sections of the formula. That seems to do the trick.

Thank you so much for all of your help!

Amanda

**Azam Ali** · 07-22-2011, 02:38 AM

I is recommended to always use the evaluate formula option in the formula tab for the understanding of the formula

It is nice that it is working for you.