Again, a picture tells a thousand words. The following GIF shows what we want to achieve:

Seriously? A simple VLOOKUP formula stated below would do. What’s the challenge?

=VLOOKUP(G3,B:E,4,FALSE)

Yes, you are right if all these tables reside on the same worksheet. What if they are sitting on different worksheets? or even on different columns?

I would say it is super challenging to create a VLOOKUP formula to lookup values from different tables in the old days. Nevertheless, with Excel 365, it’s easy!

You may download the sample workbook to follow along.

The formula is as simple as this:

=VLOOKUP(G3,VSTACK(tb_HR,tb_FIN,tb_MKTG,4,FALSE)

Such a simple and clean formula! Isn’t it?

Indeed, this is nothing but a regular VLOOKUP formula, with four arguments:

• The lookup value, G3, i.e. the Staff ID in our example
• The table array, with the secret sauce – VSTACK
• The columns index
• The match mode (true for approximate; false for exact)

Not familiar with VLOOKUP, you may refer to this blogpost.

Let’s have a closer look at one of the new functions in Excel for Microsoft 365, VSTACK.

The syntax is simple:

=VSTACK(array1,[array2],...)

By its name, it stacks (appends) arrays (in simple term, tables) into a single array (table) vertically

In our example, no matter where the three tables reside, the VSTACK appends them together in one single table (array), which is then fed into the second argument of VLOOKUP. The VLOOKUP just performs its task as usual. Super!

To make sure it works the way we expected, the order of columns across the tables must be consistent. In our example, all tables must have the four columns (Staff ID, Dept, Date Joined, Name) in the same order. By the way, did you notice that one of the column headers are different in our example? It is good and bad. VSTACK just stacks tables together, with no intelligence to determine the contents of the tables. So it doesn’t care about the column headers (unlike what is required in appending tables in Power Query).

VLOOKUP has a subling, HLOOKUP. So does VSTACK. If you happen need to perform HLOOKUP across tables, you can apply the same pattern using HSTACK. You can find an example in the sample workbook.

I think VSTACK solves many real-life problems given the fact that many Excel users would like to organize data by worksheets. A database person may argue that this is not the best practice… agree… but it is what it is. Such practice exists for a reason. Those Excel users will appreciate how VSTACK makes their working life much more easier (if they are using Excel 365 😅, as it is only avaliable in Excel for Microsoft 365 at the time of writing).

Do you have other use case of VSTACK? Please share with us by leaving comments below. 🙌