INDEX MATCH with multiple lookup_arrays/references

  • Re: INDEX&MATCH with multiple lookup_arrays/references


    Try:


    [COLOR="#0000FF"]=SUMIF($E$2:$F$8,C2,$E$3:$F$9)[/COLOR]


    Copied down

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

  • Re: INDEX&MATCH with multiple lookup_arrays/references


    No. I have multiple lookup references, i cannot use E:F range, i have other data in between.

  • Re: INDEX&MATCH with multiple lookup_arrays/references


    {=INDEX(($E$3:$F$3,$E$6:$F$6,$E$9),,MATCH(1,(C2=$E$2:$F$2)*(C2=$E$2:$F$2)&""&(C2=$E$5:$F$5)&""&(C2=$E$5:$F$5)&""&(C2=$E$8)&""&(C2=$E$8),0))}


    this formula also not working albite ctrl sh enter :(

  • Re: INDEX&MATCH with multiple lookup_arrays/references


    Please show a more representable example of your data then and why my formula doesn't work for you..

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

  • Re: INDEX&MATCH with multiple lookup_arrays/references


    I think my question is understandable. I have other number data in E4 E7..... I cannot use range E:F. Problem is in MATCH, how to lookup in E2F2, E5 F5, E8?

  • Re: INDEX&MATCH with multiple lookup_arrays/references


    I need to extract values, not Go-1, Go-2......


    Now, problem is in Index array.
    This is original formula:


    {=INDEX(($P$5:$W$5,$P$10,$P$15),,MATCH(G3,(IF(G3=$P$2:$W$2,$P$2:$W$2,IF(G3=$P$7,$P$7,IF(G3=$P$12,$P$12,"Error")))),0))}


    It only work for P5:W5, there is no data for P10 & P15 :( or values are dups from p5 w5.


    And, can this formula in any way if possible be non-volatile, non array, w/o CtrlShEnter??

  • Re: INDEX&MATCH with multiple lookup_arrays/references


    Quote from B.W.B.;772849

    I think my question is understandable. I have other number data in E4 E7..... I cannot use range E:F. Problem is in MATCH, how to lookup in E2F2, E5 F5, E8?


    Give my formula a try. It will only extract the number that is immediately below the lookup value... You will notice that the Sumif sum range is offset by one row to the criteria range... it will only take the numbers that are offset by that same one row.

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

  • Re: INDEX&MATCH with multiple lookup_arrays/references


    =IF(G3=$P$2,$P$5,IF(G3=$Q$2,$Q$5,IF(G3=$R$2,$R$5,IF(G3=$S$2,$S$5,IF(G3=$T$2,$T$5,IF(G3=$U$2,$U$5,IF(G3=$V$2,$V$5,IF(G3=$W$2,$W$5,IF(G3=$P$7,$P$10,IF(G3=$P$12,$P$15,"NA")))))))))) Arghh! No way!
    This will not work in Excel2003 but who cares:)

  • Re: INDEX&MATCH with multiple lookup_arrays/references


    Quote from B.W.B.;772971

    =IF(G3=$P$2,$P$5,IF(G3=$Q$2,$Q$5,IF(G3=$R$2,$R$5,IF(G3=$S$2,$S$5,IF(G3=$T$2,$T$5,IF(G3=$U$2,$U$5,IF(G3=$V$2,$V$5,IF(G3=$W$2,$W$5,IF(G3=$P$7,$P$10,IF(G3=$P$12,$P$15,"NA")))))))))) Arghh! No way!
    This will not work in Excel2003 but who cares:)


    I shorten this formula, with IFNA, for Excel2013 and later :)
    =SUM(IFNA(INDEX($P$5:$W$5,,MATCH(G3,$P$2:$W$2,0)),0),IFNA(INDEX($P$10,,MATCH(G3,$P$7,0)),0),IFNA(INDEX($P$15,,MATCH(G3,$P$12,0)),0))
    Thanks OzGrid for this site and inspiration!