Force INDEX SMALL ROW Array to Show Zero/Blank Rather Than 'Skipping' Result

[EduardStoo]

Hi!

I was wondering if someone could help me with this?

I need to build in a further 'IF' exception to an INDEX, SMALL, ROW, ROW, ROWS (your boat) formula, to convince the array to show a BLANK rather than 'skip' a non-applicable value, as it messes up the order.

In the attached, under the litres column, I need to total the 'litres (weight)' amount for the applicable material UNLESS it shows 'N/A: Soluble' under the FORM column to the right, whereas it then wants to be blank. The standard array skips the two 'N/A: Soluble' results (as it should), but this bunches up the info so it's not on the right lines.

I only sort of 75% know what I'm doing, so can't get my head round it! A lot of the info auto-populates from various worksheets, so it needs to be a very dynamic list!

Present formula at the moment in the litres column is:

=IFERROR(IF($S$44<>"N/A",
INDEX($W$48:$W$59,SMALL(IF($R$48:$R$59="RM",ROW($S$48:$S$59)-ROW($Q$48)+1),ROWS(B$31:$B31))),
INDEX($S$31:$S$42,SMALL(IF(($R$31:$R$42="RM"),ROW($V$31:$V$42)-ROW($Q$31)+1),ROWS(B$31:$B31)))),"")

Thanks

Stuart

[Aardigspook]

I think you've over-simplified the sample file you've attached, because the formula you give refers to empty cells on the worksheet, so although you have manually added arrows, at the moment I can't see any reason why you can't just use a more simple Index-Match combination with an IF check for 'N/A: Soluble'? For example, all the entries you want for Litres are 'RM' entries in the sample, so the need for the RM check isn't clear. Can you maybe upload a more comprehensive file (with confidential data removed or overwritten with made-up info, if necessary)?

[EduardStoo]

Thanks Aardigspook -

Sorry I was rushing it.

I've corrected the references in the attached.

The ingredients and quantities change constantly in the cells to the right, based on other sheets

Thanks,

Stuart

[Bo_Ry]

Please try at F3:G14
=IF(N(VLOOKUP($B3,$K$20:$N$24,2,)),VLOOKUP($B3,$K$20:$N$24,COLUMNS($D3:F3),),"")