If you follow the standard way to Vlookup on every other column, you may require to spend lots of time coding the formula.
Time spent on such formulas depends on the number of columns in the range.
If you have three columns to search down a key, you may require to write three Vlookup formulas.
Not a simple Google Sheets task when you have several columns to search down, right?
There are chances to mess up the ranges in the formula.
Let’s learn to correctly use Vlookup to search down every alternate column and return values from the corresponding columns in the rows found.
Assume the search key is the letter “a,” and you want to search it on columns B, D, and F and return values from columns C, E, and G.
Usually, you may write three formulas with the ranges B1:C, D1:E, and F1:G. Let’s simplify it.
Two Easy Ways to Vlookup on Every Other Column in Google Sheets
I am introducing two approaches to VLOOKUP on every other column in Google Sheets. Both are easy to follow.
But the first one is a non-array copy-paste formula, whereas the latter is an array formula.
Here you go!
Vlookup on Every Alternate Column in Google Sheets
In the above example, I have used the following formula in cell J2, which then dragged to its right and down.
=vlookup($I2,{index(B$2:$G$7,0,column(A$1)),index(B$2:$G$7,0,column(B$1))},2,0)When writing such a formula to Vlookup on every alternate column, we must carefully use the dollar signs to make the rows and columns in the cell/range references relative or absolute.
Please read that under the formula explanation below.
Formula Explanation
Syntax: VLOOKUP(search_key, range, index, [is_sorted])
search_key – $I2
In this search_key, we have used absolute column and relative row in the reference.
So nothing happens when we drag the formula to its right but the row changes when copy-pasting down.
range – {index(B$2:$G$7,0,column(A$1)),index(B$2:$G$7,0,column(B$1))}
The above ‘dynamic’ range is the key part of Vlookup on every other column in Google Sheets.
We have used two INDEX formulas to get the correct columns when we drag the Vlookup formula into its adjoining cells.
Syntax: INDEX(reference, [row], [column])
reference – B$2:$G$7 (column B adjusts when we copy to its right).
row – 0
column – Used the formula column(A$1) in the first INDEX and column(B$1) in the second INDEX.
They are equal to 1 and 2, respectively.
The 0 (zero) represents all, 1 means 1st, 2 means 2nd, and so on.
So the first INDEX returns all rows from the 1st column, whereas the second INDEX returns all rows from the 2nd column.
When we drag or copy the formula one time to its right, the first index formula returns the second column, and the second one returns the 3rd column.
Is that correct?
Yep! Please note that B$2:$G$7 also becomes C$2:$G$7, so the above pattern is OK.
index (Vlookup output column number) – 2
Vlookup on Every Other Column by Flattening Columns
Want an array formula to search down every other column?
Let’s code that below.
First of all, we require to add field labels to the table. It is a must to identify the columns in Vlookup.
I have added the labels in B1:G1 for the lookup table in B2:G7.
Please pay special attention to the labels on the top of the output table (J1:L1).
This time, we are just required to input the formula in cell J2 to Vlookup on every other column in Google Sheets.
To avoid confusion, I am coding the formula step by step.
Steps
Recently, I have shared a formula to flatten every other column in Google Sheets.
We will use that formula here.
Formula # 1 (Flatten the Lists):
=ArrayFormula(FLATTEN(filter(B1:G1,iseven(column(B1:G1))=TRUE)&"|"&filter(B2:G7,ISEVEN(column(B1:G1))=TRUE)))Formula # 2 (Flatten Prices):
=FLATTEN(filter(B2:G7,ISODD(column(B1:G1))=TRUE))For the time being, I have inserted the above formulas in cells I6 and J6, respectively.
Let’s see how to use them to Vlookup on every other column.
In cell J2, insert this Vlookup formula.
Formula # 3:
=ArrayFormula(vlookup(J1:L1&"|"&I2:I3,I6:J23,2,0))
Now we can combine the above three formulas in cell J2 as per the following syntax;
=ArrayFormula(vlookup(J1:L1&"|"&I2:I3,{formula_1,formula_2},2,0))And here it is!
=ArrayFormula(vlookup(J1:L1&"|"&I2:I3,{FLATTEN(filter(B1:G1,iseven(column(B1:G1))=TRUE)&"|"&filter(B2:G7,ISEVEN(column(B1:G1))=TRUE)),FLATTEN(filter(B2:G7,ISODD(column(B1:G1))=TRUE))},2,0))This way, we can Vlookup on every other column in Google Sheets.
Thanks for the stay. Enjoy!
Related:-
