 # INDEX MATCH with multiple lookup_arrays/references

• Hi,
please, is this [ATTACH=CONFIG]69441[/ATTACH] possible w/o using CTRL+SHIFT+ENTER? Is Match possible with multiple looking arrays?
Thanks!

## Images

• Re: INDEX&amp;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&amp;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&amp;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&amp;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&amp;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&amp;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&amp;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&amp;MATCH with multiple lookup_arrays/references

Quote from chirayuw;772872

Flipped around the IF formula

forum.ozgrid.com/index.php?attachment/69475/

{=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]

## Images

• Re: INDEX&amp;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&amp;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!