+ Reply to Thread
Results 1 to 6 of 6

VLookup: 2x2 lookups,results

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,419

    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.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-05-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    229

    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!
    Taming the Excel dragon... www.TheExcelphile.com

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,419

    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. #4
    Forum Contributor
    Join Date
    11-05-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    229

    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. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,419

    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. #6
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,419

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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