# VLookup: 2x2 lookups,results

1. ## 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.

2. ## Re: VLookup: 2x2 lookups,results

Try this formula:

``Please Login or Register  to view this content.``
Don't forget to click the little star to the left of this post if you feel I helped!

3. ## Re: VLookup: 2x2 lookups,results

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.

4. ## Re: VLookup: 2x2 lookups,results

Hi Jomili,

I think this is what you need....Todays Contracts-example.xlsb
I've created a helper column just to make the formulas easy to read...

5. ## Re: VLookup: 2x2 lookups,results

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!

6. ## Re: VLookup: 2x2 lookups,results

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1