Look up a value in a list and return ALL multiple corresponding values [closed]

I think the issue here is that the suggestion given in that link for dealing with the results of formulas in rows beyond the number of expected returns is to simply wrap the formula in some sort of IFERROR clause.

However, this set-up is extremely inefficient, particularly if the dataset in question is a dynamic, potentially expanding one.

The point is that, if you have a set-up such as:

=IFERROR([some_large_array_formula],"")

which is intended to be copied down a sufficient number of rows so as to encapsulate all desired returns, then you will be faced with two choices.

Firstly, you can perform some calculation to determine precisely how many such returns you will have at any given time, and then drag this formula down that number of rows. Obviously this is not ideal, even less so if you have, as I said, a dynamically-changing dataset.

Secondly, we can copy the formulas down to an arbitrarily large number of rows such that we are guaranteed to cover all possible returns even should our dataset expand at some future time, and so need not worry ourselves about it again.

Obviously this second method is preferable in practice. The problem with the IFERROR construction (even worse if you're on 2003 or earlier and have to use a repeated IF(ISERROR clause) is that, in rows to which the formula is copied beyond that which is effectively necessary, there is nothing to prevent the large, resource-heavy array formula calculating needlessly.

The point is that, in the above construction, even in rows beyond that containing our last expected return Excel still has to spend all the resource on calculating the array formula part before it can then decide for itself whether it is in fact an error or not.

Far, far better than this idle IFERROR approach - which is sadly almost ubiquitously recommended for this set-up around the various sources on the internet - is, as James points out, to use a single "helper" cell to first determine the number of rows which we expect to have returns, and then reference this in the formula instead.

So, for example, if the posted data was in A1:C10 (with headers in row 1) and we put e.g. NN in E1 and OUT in F1, we would first enter a single, non-array formula in e.g. G1:

=SUMPRODUCT(0+(A2:A10=E1),0+(B2:B10=F1))

The **array formula**** in our first cell of choice would then be:

=IF(ROWS($1:1)>$G$1,"",INDEX($C$2:$C$10,SMALL(IF($A$2:$A$10=$E$1,IF($B$2:$B$10=$F$1,ROW($C$2:$C$10)-MIN(ROW($C$2:$C$10))+1)),ROWS($1:1))))

and copied down as required.

Granted, we still have the question of how far to copy this formula down. And even though we have a calculation in place to determine the required number of rows, we still don't want to have to manually re-adjust the number of cells containing formulas every time we want to update results. This should be a one-off initial job.

I certainly wouldn't recommend copying down to the very end of the spreadsheet. However, provided a suitably large upper bound can be chosen, then it should not matter a great deal in terms of performance if we end up with even several thousand extraneous cells containing formulas. The reason being, and the massive difference between this set-up and the "lazy" IFERROR approach, that here the initial clause:

=IF(ROWS($1:1)>$G$1,""

means that, in rows beyond the expected number of returns, the IF clause returns TRUE and so a blank is returned. And the nice part about the IF function is that, if the clause passed to it is TRUE, then the FALSE part - here a large, resource-heavy array formula - does not even get considered for calculation.

This is not at all the case with the IFERROR version, which carries on churning away, oblivious to the fact that its calculations are needless and a burden on resource.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).