VLOOKUP multiple arguments

    I have a table as such


    [TABLE="width: 500"]

    [tr]


    [td]

    PARTNO

    [/td]


    [td]

    VOLTAGE

    [/td]


    [td]

    FLA

    [/td]


    [/tr]


    [tr]


    [td]

    P0153

    [/td]


    [td]

    208

    [/td]


    [td]

    3.3

    [/td]


    [/tr]


    [tr]


    [td]

    P0634

    [/td]


    [td]

    208

    [/td]


    [td]

    4.5

    [/td]


    [/tr]


    [tr]


    [td]

    P0783

    [/td]


    [td]

    208

    [/td]


    [td]

    6.1

    [/td]


    [/tr]


    [tr]


    [td]

    P0153

    [/td]


    [td]

    460

    [/td]


    [td]

    1.5

    [/td]


    [/tr]


    [tr]


    [td]

    P0634

    [/td]


    [td]

    460

    [/td]


    [td]

    2.1

    [/td]


    [/tr]


    [tr]


    [td]

    P0783

    [/td]


    [td]

    460

    [/td]


    [td]

    2.8

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    Notice that there are duplicates in the PARTNO column. I need to do a VLOOKUP that matches the PARTNO and VOLTAGE, then returns the FLA


    Example: Find the FLA where PARTNO="P0634" and VOLTAGE=460 ---> RETURNS: FLA = 2.1


    Or maybe there is another method instead of VLOOKUP?


    *EDIT* A VBA Approach is also acceptable

    Re: VLOOKUP multiple arguments


    Assuming data spreads from A1 to C7 try
    =SUMPRODUCT((A2:A7="P0634")*(B2:B7=460)*(C2:C7))

    Triumph without peril brings no glory: Just try

    Re: VLOOKUP multiple arguments


    If i have the following data:


    Name Ethnicity



    Tony White
    Matthew Black
    George
    Ryan
    Tony
    Paul
    Tony


    - and i want excel to copy the value that I have for Tony in ethnicity and paste in the rest of the cells corresponding to Tony that are empty how would I do that? I want do that for all names as well, not just Tony

    Re: VLOOKUP multiple arguments


    hi
    try this formula : {=INDEX(Data, MATCH(1, ((criteria range 1= criteria 1)*(criteria range 2=criteria 2)), 0), 1)}
    Ctr + Shift + Enter after input formula

    Re: VLOOKUP multiple arguments



    =INDEX($C$2:$C$7,MATCH(C11&D11,$A$2:$A$7&$B$2:$B$7,0))
    [TABLE="width: 123"]

    [tr]


    [TD="class: xl65, width: 64, align: right"]CNT+Shift+Enter
    [/TD]

    [/tr]


    [/TABLE]

Participate now!

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

Register Yourself Login