If you’ve been using Google Sheets for a while, you probably know about the Vlookup function. It’s great for doing one-way lookups and returning data from a single column. But did you know that you can also use Vlookup to perform a two-way lookup and retrieve data from two columns? In this article, I’ll show you how.
How to Get Two Column Output in a Two-Way Lookup in Google Sheets
Performing a two-way lookup in Google Sheets can be a bit tricky, especially when you’re unsure about the index column number. In this case, you can’t simply use the curly braces like you would in a one-way lookup. However, there is a workaround.
Two-Way Lookup with Output from One Column
Let’s say you want to search down column A for “Employee 2” and across the row A1:G1 for “Wed, 2 Jan 19.” To retrieve the data from cell D4, you can use the following formula:
=VLOOKUP("Employee 2", A1:G, MATCH(date(2019,1,2), A1:G1, FALSE), FALSE)This formula would return the value 8, which is in cell D4. But what if you want to retrieve the values from cells D4 and E4, i.e., two column data? That’s where the two-way lookup comes in.
Two-Way Lookup with Output from Two Columns
Here’s a handy tip to get a two column output in a two-way lookup in Google Sheets. Start with the Match formula from the previous example:
=MATCH(date(2019,1,2), A1:G1, FALSE)By using the ArrayFormula function and a virtual array with curly braces, you can modify the Match formula to return the index column numbers:
=ArrayFormula(MATCH(date(2019,1,2), A1:G1, FALSE) + {0,1})This formula returns the index column numbers 4 and 5, which correspond to columns D and E. Now you can use this in your two-way lookup formula:
=ArrayFormula(VLOOKUP("Employee 2", A1:G, MATCH(date(2019,1,2), A1:G1, FALSE) + {0,1}, FALSE))And voila! You’ll retrieve the data from cells D4 and E4.
To return multiple columns, simply adjust the virtual array in the formula. For example, if you want a 4 column output, you can use:
=ArrayFormula(VLOOKUP("Employee 2", A1:G, MATCH(date(2019,1,2), A1:G1, FALSE) + {0,1,2,3}, FALSE))This Vlookup formula would return a 4 column output. But if you need even more columns or ‘n’ columns, there is another approach you can try. Check out my article on Two-way Lookup and Return Multiple Columns in Google Sheets for more details.
And there you have it! With these tips, you can unleash the full power of two-way lookup in Google Sheets. Happy data crunching!
For more tips and tricks on Google Sheets, visit Crawlan.com.
