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.
Sometimes, however, we may be in a situation where, for whatever reasons, we are unable to use helper columns in our worksheets. This may seem like an unlikely scenario, especially when you consider that these additional columns can of course be hidden, if desired. However, given that the use of VLOOKUP requires that these additional columns be inserted to the left of the columns containing the values to be returned, it is perhaps understandable that this may not always be desirable.
As such, I will also present a solution which returns a value from multiple worksheets based on more than one criterion and in which we forgo the use of helper columns.
For example, given the following three worksheets:
we will generate the return from the Amount column from a given sheet for two different situations: firstly, and to be discussed below, based on a single criterion, i.e. where the entry in the Colour column is “Red”. And, secondly, in the follow-up post to this one, based on two criteria, i.e. where the entry in the Colour column is “Red” and the entry in the Year column is 2012.
In the latter case I will present, as mentioned, two solutions, one with the use of helper columns, the other without. Our Master sheet will thus look something like this (you can download the workbook here):
For this instalment let’s look at the first (and probably most common) scenario, then, in which we have a single criterion to check against each of our worksheets.
The required solution, to give the corresponding value from the Amount column where the entry in the Colour column is “Red”, from whichever happens to be the first worksheet (in the sense of progressing from leftmost to rightmost) where that criterion is met, is the following array formula:
where Sheets is a Defined Name referring to:
Of course, we can also store these sheet names in cells in the actual worksheet. Sometimes this necessitates an amendment to the formula syntax (e.g. depending on whether we store those sheet names in a vertical or horizontal range), though with this particular construction there is no difference.
Hence, if readers prefer to do it that way, they could define Sheets rather as, e.g.:
where we put “Sheet1”, “Sheet2” and “Sheet3” in F1, F2 and F3 of the Master sheet respectively, or, as:
where we list those same sheet names in the horizontal range H1:J1.
As mentioned, readers should note, however, that it is not always the case that we are at such liberty to choose any of the above three options without making any amendments to the required formula.
I should also point out that readers may well come across a non-array version of this formula which uses an extra INDEX function to coerce the array, though this is simply a matter of taste (readers may see here for a discussion on this property of INDEX if they wish).
How does it work?
In essence the VLOOKUP here is no different than usual. What we first need to do is to determine in which of the three sheets we are going to be performing our search. In order to do this we make use of a function whose ability to operate over three-dimensional ranges lends itself very well to our problem: and that function is COUNTIF.
Here, then, this part:
resolves to, after first inserting our Defined Name Sheets:
We then catenate each of these sheet names, at the front with a single apostrophe (not strictly necessary if the worksheet names in question contain no spacing, though obviously good practice in any case), and at the back with a string consisting of an apostrophe, an exclamation mark and a reference to the column in which we will be performing the search, all of which generate an array of text strings – which are also syntactically correct range references – to pass to INDIRECT, viz:
INDIRECT instructs Excel to interpret the elements within this array of text strings as range references, which are then passed to COUNTIF with our value in A3, i.e. “Red”, as the criteria parameter, so that the above results in the following array:
which readers can easily verify represents the fact that “Red” appears zero, one and three times in Sheet1, Sheet2 and Sheet3 respectively.
Since we are interested in the first such occurrence, we pass this array to MATCH, so that:
which is clearly 2.
We therefore know that we will be performing our VLOOKUP in the second of our sheets as listed, i.e. Sheet2.
We now pass this value of 2 as the parameter in an INDEX function with our list of sheets as the array, so that:
All that we now require is a further construction with INDIRECT to generate the correct syntax for our range to pass to VLOOKUP, so that:
and, the INDIRECT aside, this is now a recognizably standard VLOOKUP, which readers can easily verify results in 55, as desired.
Follow-up post to follow shortly, in which I will look at the slightly more complex case (at least if we do not wish to use the helper-column set-up) where we have more than one criterion to consider.