+ Reply to Thread
Results 1 to 3 of 3

Add a 3rd lookup to a formula with 2 current matches

  1. #1
    Registered User
    Join Date
    10-01-2008
    Location
    California
    Posts
    17

    Add a 3rd lookup to a formula with 2 current matches

    Hi all, a while back one of you gave me an awesome formula that pulls data from sheet 2 based on what was typed in A1 and A2 on sheet 1.

    Well I ran into a snag, is there a way we can add a 3rd match in the formula. See the attached file.

    I need to pull data from sheet 2 based on what is typed on sheet 1 in cell A1, A2 and A3.

    Exp
    Sheet 1, report to be printed
    A1 = last name
    A2 = first name
    A3 = month

    Sheet 2, data is held here
    Need to pull data in column “M”


    Thanks for the help

    MS Excel 2002 SP3
    Windows XP Pro
    Attached Files Attached Files
    Last edited by jrtulare185; 01-26-2010 at 05:38 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Add a 3rd lookup to a formula with 2 current matches

    Two ways...

    1) Expand the current formula in A1 to:
    =INDEX(DATA!$M$1:$M$1999,MATCH(G4 & A4 & C4, INDEX(DATA!$A$1:$A$1999 & DATA!$I$1:$I$1999 & DATA!$J$1:$J$1999, 0), 0))


    ...or better:
    2) Add a key column in column P of the DATA sheet. Put this formula in P2 and copy down:
    =A2&I2&J2

    Then change the A1 formula to the much simpler:
    =INDEX(DATA!$M:$M, MATCH(G4 & A4 & C4, DATA!$P:$P, 0))

    The second technique should expand to 10s of thousands of rows with no discernible performance slowdown.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-01-2008
    Location
    California
    Posts
    17

    Re: Add a 3rd lookup to a formula with 2 current matches

    works great. Thanks for the help

+ 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