I have an Index Match function that doesnt seem to be picking up all values
Please reference the "Orange" highlighted cells on Sheet 1 & 2
Sheet 2 is an example of several sheets all of which have the same format
Sheet 1 is only 1 sheet as a source
Also if an easier way exists please let me know :) always trying not to slow down a WB with formulas
IndexMatch
-
JoeExcelHelp
- 5StarLounger
- Posts: 1177
- Joined: 22 Jul 2013, 18:29
IndexMatch
You do not have the required permissions to view the files attached to this post.
-
HansV
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IndexMatch
Can't you place the second range on Sheet1 below the first one instead of to the right of it? You would then have a single lookup range.
Best wishes,
Hans
Hans
-
HansV
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IndexMatch
See the attached version in which I have moved the second range on Sheet1 below the first one.
The formula in Sheet2!K9 is
=SUMPRODUCT(Sheet1!$D$2:$K$53,(Sheet1!$A$2:$A$53=$A$1)*(Sheet1!$B$2:$B$53=$B9)*(Sheet1!$C$2:$C$53=$C9)*(Sheet1!$D$1:$K$1=INDEX(K$20:K$23,MATCH($D9,$B$20:$B$23,0))))
and this formula can be copied/pasted to all cells in Sheet2!E2:L17.
The formula in Sheet2!K9 is
=SUMPRODUCT(Sheet1!$D$2:$K$53,(Sheet1!$A$2:$A$53=$A$1)*(Sheet1!$B$2:$B$53=$B9)*(Sheet1!$C$2:$C$53=$C9)*(Sheet1!$D$1:$K$1=INDEX(K$20:K$23,MATCH($D9,$B$20:$B$23,0))))
and this formula can be copied/pasted to all cells in Sheet2!E2:L17.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans