Hi Excellers today let’s flex our formula muscles and tackle a VLOOKUP question, how to carry out a VLOOKUP over multiple worksheets in the same workbook.
Suppose I have three worksheets with data sets in all of them, each corresponding to a months worth of invoice data. I want to lookup a range of invoice numbers and return the value for each of them, and they could be in any of three invoice data worksheets in my Excel workbook.
Let’s do a quick recap of the VLOOKUP Syntax
The Syntax Of VLOOKUP Formula.
VLOOKUP( value, table, index_number, [approximate_match] )
Where
value – this is the value to search for in the first column of the table
table – this is your data set usually at least 2x columns of data
index_number – this is the columns number in the above Table from which the matching value is to be returned. Note the first column in the Table is 1.
approximate_match – this is optional. Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this argument is omitted, TRUE is the default
Putting The VLOOKUPS Formulas Together.
So in our example…………
B3 is the value we want to search for in our data sets
Month1, Month2 and Month3 are the sheet names with references $E$3:$F$15 that are the ranges or tables we want to search
2 is the column number (index) that the matched value returns
=IFERROR(VLOOKUP(B3,Month1!$E$3:$F$15,2,FALSE)
,IFERROR(VLOOKUP(B3,Month2!$E$2:$F$15,2,FALSE)
,IFERROR(VLOOKUP(B3,Month3!$E$2:$F$15,2,FALSE),0)))
The formula is simply three VLOOKUP formulas, that are connected by the IFERROR formula. All we need to do is enter the Invoice Number into cell B3 and the VLOOKUPS will return the corresponding value if it finds the Invoice Number on any of the 3 worksheets in our formula.
It just needs to be tested. Does it work? Course it does!
Over To You!
Have you a better solution for this? Would you use a VLOOKUP formula like this?.
If you want see see all of the blog posts in the #formulafriday series then you can click on this page right here.
If you want MORE Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.
