Dynamically Change the Search Column in VLOOKUP in Google Sheets

In a vertical lookup using the VLOOKUP function, Google Sheets always searches the first column in the specified range. But what if you want to search using the second, third, or any nth column? In this tutorial, you’ll learn how to dynamically change the search column in VLOOKUP using a dropdown in Google Sheets.

This method is useful when you want to search by different fields—such as Item Name or Item Code—while returning values like Stock or Price, all from a single formula setup.

Note: For this method to work, all columns in the range should have unique header names.

Table of Contents

Use Case: Lookup by Item Name or Item Code

Assume you want to return the Price of an item based on either its Item Name or Item Code. Normally, you’d need separate VLOOKUP formulas for each field. But with a simple dropdown and dynamic formula, you can switch the lookup column on the fly.

Sample Data

Item Name	Item Code	Stock	Price ($)
Dragon Fruit	DR001	70	6.5
Kiwi	KI002	80	1
Guava	GU003	60	2.5
Papaya	PA004	50	3.5
Plum	PL005	90	1

This table is placed in cells A1:D6.

Click cell F1.
Go to Insert > Dropdown.
In the sidebar:
- Replace “Option 1” with Item Name
- Replace “Option 2” with Item Code
Click Done.

Dropdown to switch the search column dynamically in VLOOKUP in Google Sheets

This dropdown lets you choose which column to search by.

Step 2: Create a Dynamic Lookup Range

Since VLOOKUP only searches the first column of a range, we’ll use a formula to rearrange the columns dynamically, putting the selected search column first.

HSTACK(CHOOSECOLS(A1:D, MATCH(F1, A1:D1, FALSE)), A1:D)

What this does:

MATCH(F1, A1:D1, FALSE) finds the position of the selected header (e.g., “Item Code”).
CHOOSECOLS(...) extracts that column.
HSTACK(..., A1:D) places the selected column before the full data table, effectively making it the first column for VLOOKUP.

F1 = Dropdown value
A1:D = Original data range
A1:D1 = Header row

Step 3: VLOOKUP Formula for Dynamic Lookup

Now that we’ve constructed the dynamic range, you can use it in your VLOOKUP formula:

=VLOOKUP(F2, HSTACK(CHOOSECOLS(A1:D, MATCH(F1, A1:D1, FALSE)), A1:D), 5, FALSE)

Example of dynamically changing the search column in VLOOKUP in Google Sheets using a dropdown

How it works:

F2 = the value you’re searching (e.g., “GU003” or “Guava”).
The range dynamically changes based on the column selected in F1.
We use column index 5 to return the value from the 4th column (Price) in the original data—because we’ve prepended a new first column.

Tip: You must use index + 1 since the search column is virtually added at the beginning.

Formula Breakdown

HSTACK(
  CHOOSECOLS(A1:D, MATCH(F1, A1:D1, FALSE)),
  A1:D
)

MATCH(F1, A1:D1, FALSE): Finds the index of the selected header in the dropdown.
CHOOSECOLS(…): Extracts that column.
HSTACK(…): Stacks the selected column before the full table.

That’s why we adjust the column index in VLOOKUP to account for the added column.

Alternative to CHOOSECOLS

Instead of CHOOSECOLS, you can also use:

INDEX(A1:D, 0, MATCH(F1, A1:D1, FALSE))

FILTER(A1:D, A1:D1=F1)

These produce the same dynamic column for the lookup but may be more familiar depending on your experience.

Alternatives to VLOOKUP for Dynamic Search Columns

Other functions like XLOOKUP, FILTER, or INDEX-MATCH can also do dynamic lookups. Here’s how:

1. Using XLOOKUP

=XLOOKUP(F2, CHOOSECOLS(A1:D, MATCH(F1, A1:D1, FALSE)), D1:D)

Dynamically selects the search column using CHOOSECOLS.
Returns values from the Price column.

2. Using FILTER

=FILTER(D2:D, FILTER(A2:D, A1:D1=F1)=F2)

The inner FILTER extracts the search column.
The outer FILTER returns matching values from column D (Price).

3. Using INDEX-MATCH

=INDEX(D2:D, MATCH(F2, INDEX(A2:D, 0, MATCH(F1, A1:D1, FALSE)), FALSE))

Dynamically identifies the search column.
Matches the value in F2.
Returns the result from column D.

Limitations of Other Methods

With these alternative methods, you must fix or modify the result range manually (like D2:D). In contrast, with the VLOOKUP + HSTACK approach, you can just change the column index number to get results from any column.

Conclusion

The classic VLOOKUP is limited to searching only the first column in a range—but with the help of CHOOSECOLS, MATCH, and HSTACK, you can dynamically change the lookup column based on a user-selected field. This makes your spreadsheet more flexible and reduces the need for multiple formulas.

For most lookup tasks where search columns can change, this dynamic VLOOKUP setup is efficient, scalable, and easy to use—especially in dashboards or inventory trackers.