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:I have the double Iferror in case both result in an error.Please Login or Register to view this content.
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.
Bookmarks