Lookup Range within a Range

  • Hi
    I have following problem statement -
    Search for the combination of City and State in another Range of MCity and MState, such that if the combination matches then, pick the corresponding MRate and multiple with the Factor; do a sum of product and produce the result.
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]City[/TD]
    [TD="width: 64"]State[/TD]
    [TD="width: 64"]Factor[/TD]

    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    X

    [/td]


    [TD="align: right"]2[/TD]

    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    Y

    [/td]


    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    C

    [/td]


    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    D

    [/td]


    [td]

    D

    [/td]


    [TD="align: right"]2[/TD]

    [/tr]


    [/TABLE]
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]MCity[/TD]
    [TD="width: 64"]MState[/TD]
    [TD="width: 64"]MRate[/TD]

    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    Z

    [/td]


    [TD="align: right"]10[/TD]

    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    Y

    [/td]


    [TD="align: right"]20[/TD]

    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    C

    [/td]


    [TD="align: right"]15[/TD]

    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    X

    [/td]


    [TD="align: right"]40[/TD]

    [/tr]


    [/TABLE]

    Expected Result = Matches (AX, BY, CC) ; Hence MRates 40,20,15. When Multiplied by Factor and Added shall give - 40*2+20*1+15*0=100


    Note - City, State, Factor, MCity, MState and MRate they all are dynamic named range.


    I tried to use, MATCH but MATCH needs a fixed Lookup-Value (doesn't accept named range).


    tried using AGGREGATE but Aggregate does SUM using Reference and not the Array.


    I tried using SUM but this worked only for the first searched value.
    SUM(IFERROR(IF(Factor>0,Factor*(IFERROR(MRate/((MCity=City)*(MState=State)),0))),0))

Participate now!

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