Look up value in row, return same COLUMN different ROW...

Hi All

I have now spent a few hours pouring over the formula explanations listed on over a dozen different websites, including Microsoft Office Help. At this point I'm completely stumped and I've held off posting it on here because I can't help but get the feeling it's something incredibly simple.

I need a formula that will locate the word "Paid Off" in a specific row ranging from B:230 to IV:230 (horizontal search). The value "Paid Off" can change it's location horizontally along B230 to IV230 depending on the amount of money I punch in to the "extra payments" column above which re-amoritizes the results.

When the formula finds the FIRST cell that says "Paid Off," I want it to reference the value in that SAME column (vertical search) but in row 33. The values in row 33 are months of the year ranging from "May 2013" (B33) to "April 2024" (IV33).

The idea is for me to easily be able to see the changing months (earlier or later) of our last debt we're trying to pay off (mortgage) as I adjust the amounts of money we put into our debt using the debt snowball tool (use the previous payment of the previous debt that is now paid off to apply to the next debt, so on, and so forth).

This seems SO simple and yet the following formulas have NOT worked!

Formula:
=LOOKUP("Paid Off",B230:IV230,B33:IV33)

Result:
Returns value in row 33 but always the LAST column, IV33

Formula
=HLOOKUP(Paid Off,"B230:IV230,B33:IV33), =VLOOKUP(Paid Off,"B230:IV230,B33:IV33) =HLOOKUP("Paid Off",B230:IV230,33)

Please put me out of my misery and clue me in as to what I'm missing.

Thanks!!!

Jake

*blinks rapidly as he stares at computer*

...that was quick. THANK YOU!!

Now for extra credit...mind making sure I understand that formula? The formula looks for the value in the date row (B33:IV33) and cross references that with the value located in the row I'm searching for an exact "match" of "Paid off." If it doesn't find "Paid off" in row 230, it will return a value of zero...

If that's correct, then answer this for me...is the reason that the vlookup and hlookup functions aren't working because it appears they can't search in two directions?

In the future, when using the index function do does it matter which direction the function searches first?

Thanks again, Martin!

Originally Posted by jgarrison

[...]The formula looks for the value in the date row (B33:IV33) and cross references that with the value located in the row I'm searching for an exact "match" of "Paid off." If it doesn't find "Paid off" in row 230, it will return a value of zero...

On failure, it should return #N/A. For that...

Either:

=IFERROR(INDEX(B33:IV33,MATCH("Paid Off",B230:IV230,0)),0)

Or:

=LOOKUP(9.9999999999999E+307,CHOOSE({1,2},0,INDEX(B33:IV33,MATCH("Paid Off",B230:IV230,0))))

Or:

=IF(ISNUMBER(MATCH("Paid Off",B230:IV230,0)),INDEX(B33:IV33,MATCH("Paid Off",B230:IV230,0)),0)

Or (Possibly):

=SUMIF(B230:IV230,"Paid Off",B33:IV33)

"[T]he reason that the vlookup and hlookup functions aren't working" is that the match and result vectors are not properly located for them to work. With INDEX/MATCH, it doesn't matter how they are located. Of course, the orientation must be the same for all of these functions.