VLookup: 2x2 lookups,results

Here's the outline of the problem:

I have a document (MyProj), which has a Contract Number. I need the Max Amount for that contract.
I have a document (Today's Contracts) which has in column A "Contract #", and in column B "Other Contract #". Many of our contracts have 2 different contract numbers, and either can show in MyProj as "Contract #". So, I need to do an either/or on my lookup. I can do that via the formula below:

Please Login or Register  to view this content.

I have the double Iferror in case both result in an error.

Now, the tough part is that my Max Amount might exist in one of two columns; "Max Contract Amt", or "Max Single Region". I'd like to first look up in the "Single Region", and if that's a 0 amount or no amount, then search in "Max Contract". But I'm lost in how to create such a complicated formula. Can I get some help on this one? I've attached a cheap example.

TheEx,
I think the formula is doing the exact opposite of what I want. It's bringing in the Max Contract amount, whereas as stated above I'd like to first look up in the "Single Region" column, and if that's a 0 amount or no amount, then bring in the "Max Contract" amount.

Okay, trying to understand...

The MATCH function in D1 is finding either a match in A, or a match in B, and returning the line position (it's the 2465th line in the range B2:B3532, whereas the match is on line 2466 of the worksheet).

The formula in C1 is diving down the range AF2:AF3532 to the line number returned in D (2465), looking at the value there, and if it's greater then 0, putting that value, and if it's NOT greater than zero, it's pulling in the amount on that line in column R.

Is that right? If so, then the formula could be written as

Please Login or Register  to view this content.

Wow, that's a hairy formula when you look at it like that. Thanks for breaking it down.

I'm about to leave for the day, so let me play with this one. I think it'll work, but I'll tell you for sure tomorrow.

Thanks!

I just realized I never gave an update on this one. TheEx's formula did the trick! I'd like to be able to do it without the helper column, but if that's what I have to do I'll do it.

Thanks!