Index Aggregate formula. Need expert help.

asked 2019-03-19 21:19:04 +0200

kevingrg77 gravatar image

updated 2019-03-20 04:26:57 +0200

I hope someone can help me out with this.. The formula (listed below)works great in Excel but in Calc I get $Div/0! Errors. I think the problem is with the "aggregate" portion.

Here is the formula:

=IF(ROWS($AH$4:AH4)<=$AH$3,INDEX($S$4:$S$16,AGGREGATE(15,3,($R$4:$R$16=$AI$3)/($R$4:$R$16=$AI$3)*(ROW($R$4:$R$16)-ROW($R$3)),ROWS($AH$4:AH4))),"")

A shortened version of the spreadsheet is attached for download.

The picture illustrates what I'm trying to accomplish. I need to be able to change the value in AI3 (currently "3") and have the list update accordingly. Open in Excel and it works great. Open in Calc and I get errors. I need this for work and I only have Calc available there. Any help is very much appreciated!

C:\fakepath\PARTFINDER.ods

image description

edit retag flag offensive close merge delete