I recently made the post here, in which I presented a solution to the problem of returning a value based upon matching a single criterion in a given column across multiple worksheets.
In this follow-up post I will look at the analogous case in which we are not matching a single criterion, but several. As mentioned in the first instalment, I will look at two solutions to this problem, one in which we make use of an extra “helper” column in each of the relevant worksheets, and one in which we do without such aids.
Most people with an average level of ability in Excel are perfectly capable of using VLOOKUP when this operation is performed over a range within a single worksheet.
But what happens when we wish to extend our search to multiple worksheets, and so return the first match from whichever sheet happens to be the first which contains our search value(s)?
In this post I will present a solution for such cases in which we have a single criterion to be matched in a given column across multiple worksheets.
In the next instalment (to follow shortly) I will also look at cases in which we are not matching a single criterion, but several. In this situation by far the simplest method is to use an extra “helper” column in each of the relevant worksheets in which we first perform a concatenation of the fields of interest. By doing this we ensure that it is a relatively straightforward case of extending the solution designed for one criterion to work also with multiple criteria.
Perhaps one of the most widely-believed myths surrounding INDEX is that it is not possible to coerce a return of more than one value from the array passed to it.
At first sight, and after various attempts at coercing such a return (including array-entry), it does indeed seem for all the world that INDEX cannot be so induced, and stubbornly persists in returning just the first element in any array generated.
Granted, the required coercions are not obvious (the first of which is borderline tautological: the necessary initial clause, IF(1,… – or any other suitable value in place of 1 – is self-evidently TRUE), though, for example, the following non-array formula: