VLOOKUP based on two conditions

  • My challenge is pulling in a data element from a file to an exisiting file based on two criteria.
    Example:
    give me the value in cell e1 if cell b1 matches AND


    File 1
    A B C
    1 H12377 03/05/2013 123
    2 H12377 03/27/2013 276
    3 H32389 05/03/2013 335


    File 2
    H12377 03/27/2013 _________


    how do I get C2 value from file 1 (276) into file2 since column A is not unique but column A plus Column B is unique?

  • Re: VLOOKUP based on two conditions


    What's your AND...?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: VLOOKUP based on two conditions


    There should be a better way, but I do something like this:


    in file 1:
    I would insert a column before "A" and use the formula A1=B1&C1, then copy paste to the end


    in File 2:


    column C formula = A1&A2
    column D formula = vlookup(C1,[file1]A:D,4,0)



    there might be a better answer but this makes the work

  • Re: VLOOKUP based on two conditions


    in file1
    B6 = H12377
    B7 = 27/03/2013 (the date to use in the right format)
    Somewhere = =SUMPRODUCT((A1:A3=B6)*(B1:B3=B7)*(C1:C3))
    Then cut and past the last formula in file2
    It gives
    =SUMPRODUCT(([VlookUp2conditionsFile1.xlsx]File1!A1:A3=[VlookUp2conditionsFile1.xlsx]File1!B6)*([VlookUp2conditionsFile1.xlsx]File1!B1:B3=[VlookUp2conditionsFile1.xlsx]File1!B7)*([VlookUp2conditionsFile1.xlsx]File1!C1:C3))

  • Re: VLOOKUP based on two conditions


    Robert's formula must be confirmed using Control + Shift + Enter for arrays formulas

    Triumph without peril brings no glory: Just try

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!