How to Get Dynamic Search Column in Vlookup in Google Sheets

Published on

In a vertical lookup, using the Vlookup function, the first column in the range will always be the search column. In certain cases, you may want to use the second, third or any nth column as the search column. Let’s see how to get dynamic search column in Vlookup in Google Sheets.

In my approach that using the nth column as the Vlookup search column, all the columns in the range must have unique column names (field labels).

You May Like: Column Heading | Column Label | Column Name | Field | Field Label in Google Sheets.

I am taking into account the following two scenarios in my examples to the dynamic search column in Vlookup.

  1. Search column controlled by the input value in a cell.
  2. Search and Index (output) columns controlled by input values in cells.

I am going to explain this under separate subtitles below.

Search Column Controlled by an Input Cell in Vlookup

Assume you want to return the price of an item based on either item name or item code.

In such a case, if the first column is for item name and the second column is for item code, how to dynamically control the search columns in Vlookup between these two columns.

Here is one example (GIF) to dynamic search column in Vlookup in Google Sheets.

Example to Dynamic Search Column in Vlookup in Google Sheets

Vlookup picks the search column based on the input value in cell E2 which is the column name. How? Please read on to know about that more.

Using the Filter function we can filter a single column or row. Here is an example to single column filter in Google Sheets.

Example to Single Column Filter in Google Sheets

Let’s use the above-provided sample dataset. The following formula filters column 2 in the range A2:D for the value 2.

=filter(A2:D,B2:B=2)

The above filter formula will populate the below single row output.

Gravel 10-20 mm225002.25

Example to Single Row Filter in Google Sheets

Unlike the single column filter, the filter criteria range will be a single row here. The below formula filters the first row (A2:D2) for the value “Item Code” which is column name.

=filter(A2:D,A2:D2="Item Code")

The above filter formula will return a single column output.

Item Code
1
2
3

To get a dynamic search column in Google Sheets Vlookup, we can use the above second filter but with some minor modification.

In the second filter, the criterion is “Item Code”. Assume that criterion is in cell E2. Then the Filter would be as below.

=filter(A2:D,A2:D2=E2)

So far so good, right?

We have now one column that changes based on the value in E2. join the Vlookup Index column to this output using Curly Braces. Then we will have a two-column array to use as Vlookup range.

I am joining the “Unit Price” column, which is going to be my Vlookup output column, here.

={filter(A2:D,A2:D2=E2),D2:D}
Filter Rows/Columns Using Filter Function

We have now a dynamic range to use in Vlookup. Here is that Vlookup formula with dynamic search column in Google Sheets.

=vlookup(E3,{filter(A2:D,A2:D2=E2),D2:D},2,0)

Put your Vlookup criterion, i.e. search key, in cell E3 based on the column you choose in cell E2. That’s all.

Dynamic Search and Index Columns in Vlookup in Google Sheets

This is a more advanced version of the above Vlookup. Here in addition to the search column, the index column, i.e. output column, is also dynamic.

Here we must make some changes in the Filter formula as below. Instead of the above Filter, we can use this alternative formula that contains Regexmatch.

=filter(A2:D, regexmatch(A2:D2, "^"&E2&"|Unit Price$"))

Related Reading: Regexmatch in Filter Criteria in Google Sheets [Examples].

This Filter and Regex combo has one advantage. It can dynamically control the search as well as the Index columns in Vlookup in Google Sheets.

Right now it’s not dynamic. It’s just a replacement to the filter we have used in the earlier Vlookup.

How to make this filter dynamic?

First, replace “Unit Price” (output column) in the regex with the cell reference F2. Then enter the key “Unit Price” in cell F2.

=filter(A2:D, regexmatch(A2:D2, "^"&E2&"|"&F2&"$"))

Use this as the Vlookup Range. You have done!

Dynamic Search and Index Column in Vlookup

How to use this Vlookup formula dynamically?

  • To change the search column dynamically, change the value in E2. In E2 use either “Item Name” or “Item Code”.
  • To change the Index/output column dynamically, change the value in F2. in F2 use either “Quantity” or “Unit Price”.

Follow this method to control the Vlookup search column and index column dynamically in Google Sheets.

Additional Resources:

  1. Vlookup to Find Nth Occurrence in Google Sheets [Dynamic Lookup].
  2. Dynamic Index Column in Vlookup in Google Sheets.
  3. Dynamic Column in Vlookup Importrange Formula in Google Sheets.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.