Hi,
please, is this [ATTACH=CONFIG]69441[/ATTACH] possible w/o using CTRL+SHIFT+ENTER? Is Match possible with multiple looking arrays?
Thanks!
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
-
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..
-
-
Re: INDEX&MATCH with multiple lookup_arrays/references
You can always just use an IF statement. Wasn't sure which way around you needed it
-
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
Flipped around the IF formula
-
Re: INDEX&MATCH with multiple lookup_arrays/references
Quote from B.W.B.;772849I 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.
-
-
Re: INDEX&MATCH with multiple lookup_arrays/references
Quote from chirayuw;772872{=IF(G4=$P$2:$W$2,$P$5:$W$5,IF(G4=$P$7,$P$10,IF(G4=$P$12,$P$15,"NA")))} not working
Problem is in the range P2W2![ATTACH=CONFIG]69497[/ATTACH]
-
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!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!