Have you ever found yourself in the depths of a Google Sheets spreadsheet, desperately trying to perform a three-way lookup? This powerful feature allows you to search for data using not one, not two, but three search keys. It’s like having a magical key that opens hidden doors in your spreadsheet!
In Google Sheets, the key to unlock this feature lies in the Vlookup and Match functions. By combining these functions with the ampersand sign or Concatenate function, you can perform a three-way lookup with ease.
Let’s take a real-life example to illustrate how this works. Imagine you have a Genral Ledger (GL) Data sheet for a company. You need to find the value that corresponds to the company code « 1000 », the account code « 11003 », and the month « Mar ». The result, my friend, is $114,514.00, conveniently located in cell E4.
Non-Array Formula to Perform Three-Way Lookup in Google Sheets
Now, let me share with you the gorgeous formula that will make your three-way lookup dreams come true. This formula is designed specifically for Google Sheets and does not expand the results to additional rows or columns.
=ArrayFormula(vlookup(1000&11003,{A1:A&B1:B,C1:F},match("Mar",A1:F1,0)-1,0))
Here’s the magic trick: the search keys are combined using the ampersand sign, and we focus on columns A and B. It’s as simple as that!
Google Sheets Three-Way Lookup Formula Explanation
Before we move on, let’s dive deeper into the mechanics of the Vlookup function. In the GL data example above, the first search key, « 1000 », is located in column A, while the second search key, « 11003 », is in column B.
To handle this, we combine the two search keys using the ampersand sign and the columns A and B in the GL data. This way, we end up with one search key and one search column. Simple, right?
The combined search key: 1000&11003
The combined columns in GL data: {A1:A&B1:B,C1:F}
But wait, there’s more! The third search key is « Mar », which corresponds to the month in row #1. We want to dynamically return the index column in Vlookup using this search key.
match("Mar",A1:F1,0)-1
In this case, the formula returns #4, as the column that contains the March month data is column E (the fifth column). However, since we combined columns A and B, the column number for column E in the three-way lookup formula is 4, not 5. The -1 in the Match formula takes care of that.
Believe it or not, this is the easiest formula to perform a three-way lookup in Google Sheets. Simply brilliant!
Array Formula for Three-Way Lookup in Google Sheets
If you’re feeling adventurous and want to take your three-way lookup game to the next level, let me introduce you to the array formula. This little gem allows multiple row outputs in a three-way lookup.
Here’s the formula that will blow your mind:
=ArrayFormula(vlookup({1000&11003;1002&11002},{A1:A&B1:B,C1:F},match("Mar",A1:F1,0)-1,0))
When you compare this formula with the non-array formula, you’ll notice that the main difference lies in the search key usage. The search keys are now highlighted in cyan and red in both the formula bar and columns A and B. It’s like a beautiful symphony of colors!
But hey, there’s one last secret I want to share with you – how to take the criteria from cells. Prepare to be amazed!
Formula (in cell J2):
=ArrayFormula(vlookup(H2:H3&I2:I3,{A1:A&B1:B,C1:F},match(J1,A1:F1,0)-1,0))
And there you have it, my friend! The hidden magic of three-way lookup in Google Sheets. I hope you find this knowledge useful and, most importantly, enjoy the wonders it brings to your spreadsheets.
If you want to learn more about the power of Vlookup, make sure to check out Crawlan.com. They have a wealth of resources to help you become a Google Sheets wizard!
Au revoir!
