Sometimes in Google Sheets, your data isn’t neatly arranged in adjacent columns. For example, sales figures, vendor quotes, or product categories may be spread across every other column. In such cases, a standard VLOOKUP cannot directly pull multiple non-adjacent values in one formula.
In this tutorial, you’ll learn how to:
- Return values from every other column using VLOOKUP with an array of column indexes.
- (Advanced) Search for a key across multiple non-adjacent columns when your data requires it.
By the end, you’ll have flexible formulas that handle both typical and complex scenarios, saving time and making your spreadsheets more dynamic.
Return Values from Every Other Column Using VLOOKUP
Consider this list of items and their quoted prices from various vendors:
Suppose you want to lookup an item, like “Oranges,” and return the revised prices from all vendors. The revised price columns are 3, 5, and 7.
=ARRAYFORMULA(IFERROR(VLOOKUP("Oranges", A2:G, {3, 5, 7}, FALSE)))Result:
0.95 1.00 0.98Making It Dynamic for Growing Columns
If you add more vendors in the future, you’d need to update {3,5,7}. Instead, you can make it dynamic using SEQUENCE:
=ARRAYFORMULA(IFERROR(VLOOKUP("Oranges", A2:G, SEQUENCE(1, COLUMNS(A2:G)/2, 3, 2), FALSE)))SEQUENCE(1, COLUMNS(A2:G)/2, 3, 2)generates a sequence in 1 row, with as many columns as half of your range, starting from 3 and stepping by 2.- This ensures your formula automatically picks every other column, making it scalable for new vendors.
Advanced: Searching for a Key in Multiple Non-Adjacent Columns
Sometimes you need to search for a key in multiple non-adjacent columns and return a value from the column next to it. A standard VLOOKUP won’t work here.
Consider this dataset:
Suppose you want to find the prices of “Grapes” from all vendors.
⚠️ Tip: This data layout is not ideal. A better approach is to list each vendor below the previous one and use a separate column for vendor name, which allows FILTER to work cleanly.
Formula to Search Across Multiple Columns
=BYCOL(
SEQUENCE(1, COLUMNS(A2:F)/2, 1, 2),
LAMBDA(col,
VLOOKUP("Grapes", HSTACK(INDEX(A2:F, 0, col), INDEX(A2:F, 0, col+1)), 2, FALSE)
)
)Explanation:
SEQUENCE(1, COLUMNS(A2:F)/2, 1, 2)→ selects every other column containing item names.HSTACK(INDEX(...), INDEX(...))→ creates a temporary two-column array with the item and corresponding price.VLOOKUP("Grapes", ..., 2, FALSE)→ fetches the price for “Grapes” from that vendor column.BYCOL(..., LAMBDA(...))→ repeats the search across all vendor columns.
This approach allows you to search for a key in non-adjacent columns and return values from the next column.
Sample Sheet
You can open this sample Google Sheet to test both scenarios:
- Return values from every other column using VLOOKUP.
- Search for a key across non-adjacent columns using BYCOL + HSTACK.
Tip: The sheet contains the sample datasets used in this tutorial so you can try the formulas directly and see the results in action.
