CODEWITHSUNDEEP
excelexcel-formula
DFH
DFH

Reputation: 45

Using array with index match

I initially used the array {=...} with an IF statement to have different values for the max value in a column:

{=IF([@Column1]*[@Column2]=MAX([Column1]*[Column2]),1.5,0.5)}

So if the value of column 1 in that row times the value of column 2 in that same row is the max value of the same equation applied to all the rows, the value is 1.5, otherwise the value is 0.5.

However! If e.g. [Column1] is a list of strings, where I want to use INDEX(MATCH()) to lookup a value from another table, and use it in the above formula it says #VALUE!.

{=IF(INDEX(Matrix1[Column6];MATCH([@[Column1]];Matrix1[Column7];0)))*[@Column2]=MAX(IF(INDEX(Matrix1[Column6];MATCH([[Column1]];Matrix1[Column7];0)))*[Column2]),1.5,0.5)}

My guess is that the INDEX(MATCH()) is erronious when using the array brackets "{}".

I do not know how to fix this. Any help is greatly appreciated!

Upvotes: 0

Views: 92

Answers (1)

barry houdini
barry houdini

Reputation: 46331

INDEX doesn't like to return an array without some manipulation, better to do it with a SUMIF for the second part, e.g.

=IF(INDEX(Matrix1[Column6];MATCH([@[Column1]];Matrix1[Column7];0)))*[@Column2]=MAX(SUMIF(Matrix1[Column7];[[Column1]];Matrix1[Column6])*[Column2]);1.5;0.5)

confirmed with CTRL+SHIFT+ENTER

In this scenario SUMIF is being used as a "pseudo lookup" function.

Upvotes: 1

Related Questions