FILTER/INDEX value error

[excelos]

Hello

Can you tell me please why I get a VALUE error for the below?

=FILTER(INDEX('detail'!$1:$1,0,MATCH(JDM2,'detail'!$1:$1,0)),('detail'!$B:$B=JDP4:JEO4))

I am not sure where the error is.

I want to lookup JDM2 in the first row of 'detail' and then filter this whole column using the condition detail'!$B:$B=JDP4:JEO4.

I also get 'not enough memory to complete calculation' error!

Thanks!

 

[RoryA]

Your index function is only returning one cell.

 

[excelos]

I also get a VALUE error for the below:

=FILTER(INDEX('detail'!$1:$1000,0,MATCH(IAM2,'detail'!$1:$1,0)),'detail'!$B1:$B1000=$IAP$4:$IBO$4)

Any idea?

 

[RoryA]

I think your criteria needs to be more like

=FILTER(INDEX('detail'!$1:$1000,0,MATCH(IAM2,'detail'!$1:$1,0)),ISNUMBER(MATCH('detail'!$B1:$B1000,$IAP$4:$IBO$4,0)))

so you just have one TRUE/FALSE value for each row, not an array for each.

 

[excelos]

Thanks, this works but can you explain please why the 'detail'!$B1:$B1000=TRANSPOSE($JDP$4:$JEO$4) does not work and produces N/A?

I cannot see why it doesn't work.

 

[RoryA]

You’d be trying to equate a column of values to a shorter column of values, which can’t work