Index + Match Multiple Approximate Values
- Thread starter JohnW
- Start date
John
The problem is the second Match:
MATCH(C16,$C$3:$C$11,1) which returns 9
Is being used in the Column part of the Index formula
But the index array $E$3:$E$11 is only 1 column wide
Change D16:
=INDEX($E$3:$E$11,MATCH(B16,$B$3:$B$11,1),MATCH(C16,$C$3:$C$11,1))
To: =INDEX($E$3:$E$11,MATCH(B16,$B$3:$B$11,1),1)
The problem is the second Match:
MATCH(C16,$C$3:$C$11,1) which returns 9
Is being used in the Column part of the Index formula
But the index array $E$3:$E$11 is only 1 column wide
Change D16:
=INDEX($E$3:$E$11,MATCH(B16,$B$3:$B$11,1),MATCH(C16,$C$3:$C$11,1))
To: =INDEX($E$3:$E$11,MATCH(B16,$B$3:$B$11,1),1)
NARAYANK991
Excel Ninja
Hi John ,
See if this is OK.
Narayan
See if this is OK.
Narayan
NARAYANK991
Excel Ninja
Hi John ,
The formula which has been used is :
=INDEX($E$3:$E$11,MAX(IF(B16>=$B$3:$B$11,IF(C16>=$C$3:$C$11,ROW($E$3:$E$11)-MIN(ROW($E$3:$E$11)) + 1))))
The part which is highlighted in blue , is the Contribution Amounts.
The part which is highlighted in brown , is a technique to get index numbers from 1 till the number of elements within the range.
When ever the INDEX function is used , the index value cannot go below 1 , or above the number of elements in the range. Unlike for the OFFSET function , the first parameter of the INDEX function always has to be a range.
In this case , the range is $E$3:$E$11 , which means there are 9 elements in the range.
When we use ROW($E$3:$E$11) , what results is an array of numbers , starting from the first row number till the last row number ; in this case , we would get the array :
{3;4;5;6;7;8;9;10;11}
We cannot directly apply this array to the INDEX function , since the lowest index value in the array is 3 , not 1 ; the highest index value is 11 , which would result in a reference outside the bounds of the data range $E$3:$E$11.
What we would like is an array ranging from 1 to 8 , as follows :
{1;2;3;4;5;6;7;8;9}
where the lowest value 1 would refer to the contents of cell E3 , while the highest value 9 would refer to the contents of cell E11.
We get this when we use the construct highlighted in brown.
The portion MIN(ROW($E$3:$E$11)) returns the value 3 , since this is the minimum value in the array formed by the portion ROW($E$3:$E$11) , which is :
{3;4;5;6;7;8;9;10;11}
When we subtract this value of 3 from the above array , we get :
{0;1;2;3;4;5;6;7;8}
which is still not correct , since we cannot use an index of 0 to get at a cell value. Which is why we add 1.
Thus the full portion highlighted in brown :
=ROW(datarange) - MIN(ROW(datarange)) + 1
can be used with any range , and it will always return an array of increasing numbers , whose starting point is 1 , whose increment is 1 , and whose last value is the number of elements in the range.
Narayan
The formula which has been used is :
=INDEX($E$3:$E$11,MAX(IF(B16>=$B$3:$B$11,IF(C16>=$C$3:$C$11,ROW($E$3:$E$11)-MIN(ROW($E$3:$E$11)) + 1))))
The part which is highlighted in blue , is the Contribution Amounts.
The part which is highlighted in brown , is a technique to get index numbers from 1 till the number of elements within the range.
When ever the INDEX function is used , the index value cannot go below 1 , or above the number of elements in the range. Unlike for the OFFSET function , the first parameter of the INDEX function always has to be a range.
In this case , the range is $E$3:$E$11 , which means there are 9 elements in the range.
When we use ROW($E$3:$E$11) , what results is an array of numbers , starting from the first row number till the last row number ; in this case , we would get the array :
{3;4;5;6;7;8;9;10;11}
We cannot directly apply this array to the INDEX function , since the lowest index value in the array is 3 , not 1 ; the highest index value is 11 , which would result in a reference outside the bounds of the data range $E$3:$E$11.
What we would like is an array ranging from 1 to 8 , as follows :
{1;2;3;4;5;6;7;8;9}
where the lowest value 1 would refer to the contents of cell E3 , while the highest value 9 would refer to the contents of cell E11.
We get this when we use the construct highlighted in brown.
The portion MIN(ROW($E$3:$E$11)) returns the value 3 , since this is the minimum value in the array formed by the portion ROW($E$3:$E$11) , which is :
{3;4;5;6;7;8;9;10;11}
When we subtract this value of 3 from the above array , we get :
{0;1;2;3;4;5;6;7;8}
which is still not correct , since we cannot use an index of 0 to get at a cell value. Which is why we add 1.
Thus the full portion highlighted in brown :
=ROW(datarange) - MIN(ROW(datarange)) + 1
can be used with any range , and it will always return an array of increasing numbers , whose starting point is 1 , whose increment is 1 , and whose last value is the number of elements in the range.
Narayan
Hi Narayan,
Sorry for the late reply.
Thank you for your patient and detail explanation.
It really gives me a better insight of how the excel formula can works to this extend.
And also the logic adopted with the excel formula application will definitely benefits me in the application now and in future.
Thank you once again.
Sorry for the late reply.
Thank you for your patient and detail explanation.
It really gives me a better insight of how the excel formula can works to this extend.
And also the logic adopted with the excel formula application will definitely benefits me in the application now and in future.
Thank you once again.