Excel VLOOKUP Multiple Sheets

This week I had a question from Diedre asking if she can use VLOOKUP to check multiple sheets…. 17 different sheets in fact.

The idea being that if VLOOKUP doesn’t find a match on the first sheet, it will check the next sheet and so on.

The good news is we can, the bad news is it’s a bit complicated….but if you’ve only got a few sheets I‘ll show you an easier formula at the end.

Below is our table that we want to populate by looking up the Product Code in column A and return the Product Description and Price.

The challenge is that the product code information could be on Sheet2:

Or Sheet3:

Or Sheet4:

The first thing we need to do is enter a list of our sheet names somewhere in the workbook and give them a named range. I’ll give mine the very imaginative name: SheetList

Now we can enter our formula in cell B4:

=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4"),2,FALSE)

It’s an array formula so we need to enter it with CTRL+SHIFT+ENTER.

We can then copy it down the column to lookup the remaining product codes.

The above is just a small sample of the toys on my boy’s Christmas lists!

How this Formula Works

Unfortunately this formula doesn’t evaluate in order from left to right, so it’s a bit difficult to translate into English as I like to do, instead we’ll look at the separate components and understand what they’re doing.

First remember the syntax for the VLOOKUP Function is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

In our formula we know everything except the table_array argument. Remember we don’t know what sheet contains our lookup_value.

Instead we use the INDIRECT, INDEX, MATCH and COUNTIF functions to build the table_array reference dynamically for each product code in column A like this:

=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4"),2,FALSE)

1. The COUNTIF component

The COUNTIF part of the formula is checking each sheet in the SheetList to find a match. If it finds a match it counts 1, the other sheets return a zero.

=VLOOKUP("SKU001",INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT ("'"&SheetList&"'!$A$1:$c$4"),"SKU001")>0),0))&"'!$A$1:$c$4"),2,FALSE)

Our formula now looks like this:

=VLOOKUP("SKU001",INDIRECT("'"&INDEX(SheetList,MATCH(1,{1;0;0},0))&"'!$A$1:$c$4"),2,FALSE)

The {1;0;0} above represents the count of matches on the 3 rows in the SheetList. You can imagine because it found a match for SKU001 on Sheet1 it looks a bit like this:

2. MATCH Function

The syntax for the MATCH function is:

MATCH(lookup_value, lookup_array, [match_type])

From our formula: MATCH(1,{1;0;0},0)

Where zero [match_type] is an exact match.

The MATCH function is looking for the value 1 in the lookup_array {1;0;0} and returns its position. Which in this case is the first position, therefore our MATCH formula evaluates to 1 like this:

=VLOOKUP("SKU001",INDIRECT("'"&INDEX(SheetList,1)&"'!$A$1:$c$4"),2,FALSE)

Note: if it found a match on Sheet3 it would look like this:

MATCH(1,{0;1;0},0) and would evaluate to 2. i.e. the second value in the SheetList.

3. The INDEX function

INDEX Function syntax:

INDEX(reference, row_num, [column_num], [area_num])

Note: The last two arguments are optional (designated by the square brackets) and we don’t need them in this formula.

INDEX(SheetList,1)

The INDEX function uses the result from MATCH as the row number argument and returns a reference to the 1^st value in the SheetList; Sheet2

Our formula now looks like this:

=VLOOKUP("SKU001",INDIRECT("'"&"Sheet2"&"'!$A$1:$c$4"),2,FALSE)

4. The INDIRECT Function

The INDIRECT Function returns a reference specified by a text string.

Everything inside the INDIRECT formula above is text, you can tell by the double quotes surrounding it. The INDIRECT function takes that text and converts it to a reference.

COUNTIF, INDEX and MATCH have done the heavy lifting, now all INDIRECT needs to do is add the necessary apostrophes to the reference and give it to VLOOKUP.

It’s a bit difficult to see the apostrophes from the double quotes so I’ve made them red below:

=VLOOKUP("SKU001",INDIRECT("'"&"Sheet2"&"'!$A$1:$c$4"),2,FALSE)

Which becomes:

=VLOOKUP("SKU001",'Sheet2'!$A$1:$c$4,2,FALSE)

VLOOKUP Multi-cell Array Formula

Now we’ve found the product descriptions we could copy the formula into column C and change the col_index_num argument, or as Finn, my 4 year old, says ‘we could be a bit more cleverer’ and change the formula slightly so that it can find both results with the one formula!

To do this we’ll use a multi-cell array formula.

When you enter a multi-cell array formula you first select all the cells you want populated, then you enter the formula.

Since we’ve already got our formula in cell B4 we can select B4 and C4, then press F2 to edit the formula.

All we need to change is the col_index_number argument like this:

=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT ("'"&SheetList&"'!$A$1:$c$4"),A4)>0),0))&"'!$A$1:$c$4"),{2,3},FALSE)

Then press CTRL+SHIFT+ENTER and you now have your price information and you have used the same formula in both B4 and C4.

Now you can copy cells B4 and C4 down columns B and C and you’re done

The Easier Option

OK, if your head is hurting here’s an easier solution that will do if you’ve only got 2 or 3 sheets to lookup.

Warning: Any more than 3 sheets and you’re susceptible to a repetitive strain injury so it’s best to use the first solution.

VLOOKUP Multiple Sheets with IFERROR

All we do is wrap the VLOOKUP formulas in an IFERROR function.

=IFERROR(VLOOKUP($A4,Sheet2!$A$2:$C$4,2,FALSE),IFERROR(VLOOKUP($A4,Sheet3!$A$2:$C$4,2,FALSE),VLOOKUP($A4,Sheet4!$A$2:$C$4,2,FALSE)))

In English it reads:

Lookup the product code in A4 on Sheet2 and return the value in column 2 of the range A2:C4, if you can’t find an exact match in Sheet2 check Sheet3, if you can’t find an exact match in Sheet3 check Sheet4.

VLOOKUP Dynamic col_index_num

The formula above is good, but we want to copy it to column C for the price and in doing so we’d need to change the col_index_num argument.

An easier way to do this is to make it dynamic by using the COLUMNS function for the col_index_num argument like this:

=IFERROR(VLOOKUP($A4,Sheet2!$A$2:$C$4,COLUMNS($A$3:B$3),FALSE),IFERROR(VLOOKUP($A4,Sheet3!$A$2:$C$4,COLUMNS($A$3:B$3),FALSE),VLOOKUP($A4,Sheet4!$A$2:$C$4,COLUMNS($A$3:B$3),FALSE)))

The COLUMNS function returns the number of columns in an array or reference.

A3:B3 = 2 columns in the range.

Therefore COLUMNS($A$3:B$3) evaluates to 2 which is the col_index_num we want.

Note how the first cell in the range $A$3 is absolute but the second cell in the range is only absolute for the row number.

Now when I copy the formula across to column C my formula dynamically updates like so:

=IFERROR(VLOOKUP($A4,Sheet2!$A$2:$C$4,COLUMNS($A$3:C$3),FALSE),IFERROR(VLOOKUP($A4,Sheet3!$A$2:$C$4,COLUMNS($A$3:C$3),FALSE),VLOOKUP($A4,Sheet4!$A$2:$C$4,COLUMNS($A$3:C$3),FALSE)))

And COLUMNS($A$3:C$3) evaluates to 3.

Download the workbook.

If you liked this click the buttons below to share it with your friends and colleagues on Facebook, Twitter, Google+ or LinkedIn….or leave me a comment

Tagged as: vlookup