VLOOKUP Across Several Worksheets (1) – One Search Criterion 4

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:

Sheet1:

Sheet1

Sheet2:

Sheet2

Sheet3:

Sheet3

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):

Master

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:

=VLOOKUP($A3,INDIRECT("'"&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B:B"),$A3)>0,0))&"'!B1:D10"),3,0)

where Sheets is a Defined Name referring to:

={"Sheet1","Sheet2","Sheet3"}

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.:

=Master!$F$1:$F$3

where we put “Sheet1”, “Sheet2” and “Sheet3” in F1, F2 and F3 of the Master sheet respectively, or, as:

=Master!$H$1:$J$1

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:

COUNTIF(INDIRECT("'"&Sheets&"'!B:B"),$A3)

resolves to, after first inserting our Defined Name Sheets:

COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!B:B"),$A3)

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:

COUNTIF(INDIRECT({"'Sheet1'!B:B","'Sheet2'!B:B","'Sheet3'!B:B"}),$A3)

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:

{0,1,3}

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:

MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B:B"),$A3)>0,0)

becomes:

MATCH(TRUE,{0,1,3}>0,0)

i.e.:

MATCH(TRUE,{FALSE,TRUE,TRUE},0)

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:

INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B:B"),$A3)>0,0))

becomes:

INDEX(Sheets,2)

which is:

INDEX({"Sheet1","Sheet2","Sheet3"},2)

i.e. “Sheet2”.

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:

=VLOOKUP($A3,INDIRECT("'"&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheets&"'!B:B"),$A3)>0,0))&"'!B1:D10"),3,0)

becomes:

=VLOOKUP($A3,INDIRECT("'Sheet2"&"'!B1:D10"),3,0)

which is:

=VLOOKUP($A3,INDIRECT("'Sheet2'!B1:D10"),3,0)

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.

4 comments

  1. If I may offer a variation which is longer but doesn’t require CSE and uses only one instance of INDIRECT():

    =VLOOKUP($A3,CHOOSE(MATCH(1,MMULT(1,--(COUNTIF(INDIRECT("'"&Sheets&"'!B:B"),$A3)>0)),0),Sheet1!B1:D10,Sheet2!B1:D10,Sheet3!B1:D10),3,0)
  2. Thanks! An interesting construction. And agreed that one less INDIRECT can only be a good thing.

    But of course my use of 3 worksheets was merely an example. Would you still consider this method if we had e.g. 30 sheets to consider?

    Also, re the “doesn’t require CSE”, I’m not so sure that this is such a good thing as people make out. As I mention in this article, most people tend to use an additional INDEX function in this construction (as in many others, of course) to circumvent the need to use CSE. Though an extra function is an extra function (as is the MMULT here, of course), after all. And besides, why are so many so keen to avoid array formulas?

    Certainly employing the odd INDEX or MMULT function in this manner is a perfectly acceptable and reasonable technique, and I often do so myself. However, I would never go as far as to suggest that the non-array, extra-function version was an improvement over the equivalent array aet-up.

    I recently had a discussion with someone (an MVP, as it happens) who had developed a fondness for avoiding array formulas so much so that it bordered on obsession, often inserting up to two or three additional INDEX functions into a formula to get round the need for CSE. I tried to point out that, not only do all those extra INDEX functions make the formula less comprehensible (certainly to the less-experienced), but they actually cause the resulting performance to be worse than the equivalent array set-up. As I said, an extra function is an extra function.

    Anyway, I digress too much. Many thanks for your contribution! Cheers!

  3. Hi,

    In Google Sheets, you can “merge” ranges together vertically or horizontally, so the first lookup can be done with

    =vlookup(A3,{Sheet1!B:D;Sheet2!B:D;Sheet3!B:D},3,0)

    Here Sheet1 is on top of Sheet2 which is on top of Sheet 3.

    To find the first match based on color and year, you could use the QUERY function on the same range:

    =query({Sheet1!B:D;Sheet2!B:D;Sheet3!B:D},"select Col3 where Col1 = '"&A6&"' and Col2 = "&B6&" limit 1",0)

    Normally without the “limit 1,” it would return all values that match, but “limit 1” makes it so only the first value shows up.

    Link to the spreadsheet: https://docs.google.com/spreadsheets/d/1dOk7jnPKLX-x-6ufWoCijo5REX_6_yFdz8oa_uAnegE/edit#gid=1497670135

  4. Thanks again, Isai!

    Keep them coming! I’m starting to see you – no offence – as a form of “translator” here. For any readers (and I’m sure there are some) who visit this site hoping for the equivalent Google Sheets solution, your constructions are no doubt invaluable. So many thanks.

    And again, how much more straightforward it all seems compared to the equivalent Excel set-up!

    Regards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s