Using array with index match

Question

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!

barry houdini · Accepted Answer · 2018-01-19 11:31:56Z

1

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.

answered Jan 19, 2018 at 11:31

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using array with index match

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related