This post explains how to use Vlookup to find a search key in multiple columns (in a matrix) in Google Sheets. Please note the method that I am going to explain won’t work in Excel.

Let me start with the usage of Vlookup that you are familiar with. That would be easy for me to come to the topic.

As you may already aware, there are four arguments in Vlookup. They are search_key, range, index (output column), and is_sorted (data is sorted or not).

VLOOKUP(search_key, range, index, [is_sorted])

In this, NORMALLY, the ‘search_key’ (the value to find) must be from the first column of the ‘range’.

For example A2:D7 is the range. The search_key is “Green” which is available in the first column of the range.

So the following formula would return “Student 9” as the index column (output column) is 3.

=vlookup("Green",A2:D7,3,0)

If the output column is 2, then the result would be “Student 3”.

If you use the search_key from any other range, for example, “Student 1”, then the formula would return #N/A. I’m focusing on this issue in this post and I’ll come to that later.

Did you notice why I’ve stressed above “normally the search key must be from the first column”?

Because it (the search_key) can be from any specific column with a workaround (in this post we will learn how to use the search_key from any non-specific column, i.e. from a column range).

Assume the search_key is in the last column (a specific column) in Vlookup. Then how to get the same above result?

Sample Data – Range is A2:D7

Re-arrange the ‘range’ by moving the last column (D2:D7) to first that within the formula using Curly Brackets.

=vlookup("Green",{D2:D7,A2:C7},3,0)

Similar: Reverse Vlookup Examples in Google Sheets [Formula Options].

Our Scenario (Matrix Search Key Range)

Our Vlookup scenario is entirely different from the above two examples.

In our case, we want to tune the Vlookup to find the search key in multiple columns (B2:D7) then return value from the first column (A2:A7).

Sample Data Range (A2:D7):

I want my Vlookup formula to searches down a range (a matrix) for a key and return the value of a specified cell in the first column of the row found.

Here the range is A2:D7 and our search key can be from any cells in the range B2:D7. We want the output from the first column (A2:A7).

Let’s see how to tune Vlookup to find the search key in multiple columns in Google Sheets.

How to Use Vlookup with Search Key in Multiple Column Range

Since Vlookup is not able to search down multiple columns, the solution is to unpivot the dataset (range). Let me explain how.

As per the sample data above, the range B2:D7 (multiple search_key columns) must be flattened to form a single column range. So that we can ‘virtually’ use Vlookup to find search key in multiple columns range. (there will be only one single column after flattening).

The below example shows what’s happening to a range after flattening and after that, you can learn how to do it.

Data:

After Flattening:

To flatten our sample data (Vlookup search key matrix) we can use the below formula.

=flatten(B2:D7)

You can learn the use of FLATTEN here – How to Use the FLATTEN function in Google Sheets.

But just flattening the range is not enough to use the Vlookup to find the search key in multiple columns. We have to deal with the range A2:A7 too which is the output column.

See this table (first row from our sample data).

By flattening what we would get is the student names in one column. In another column (second column), we want the first column values as shown below.

Logic

If we use any student names as the searh_ky in Vlookup, the output will be “Blue”. That’s the logic of using Vlookup to find the search key in multiple columns (Matrix) in Google Sheets.

Here is the unpivot formula for using as the Vlookup range.

=ArrayFormula(split(flatten(B2:D7&"|"&A2:A7),"|"))

To understand this formula you may please check my similar tutorial – A Simple Formula to Unpivot a Dataset in Google Sheets.

Formula Example

Now (after flattening) the Vlookup search_key is not in multiple columns. It’s in the very first column as per the Vlookup standard.

Assume we want to lookup “Student 11” which is in cell C4 and return the value from the first column in the same row, which is “Yellow”. The formula to use would be as follows.

=vlookup("Student 11",ArrayFormula(split(flatten(B2:D7&"|"&A2:A7),"|")),2,0)

In this the arguments are as follows;

search_key – “Student 11”
range – unpivot formula
index – 2 (there are only two columns after flattening)
is_sorted – 0 (unsorted)

Multiple Search Keys in a Matrix

Similar to normal Vlookup we can use multiple search keys with the matrix Vlookup.

Related: How to Use VLOOKUP with Multiple Criteria in Google Sheets [Solved].

To lookup “Student 5”, “Student 10”, “Student 18”, use the formula as below.

=ArrayFormula(vlookup({"Student 5";"Student 10";"Student 18"},ArrayFormula(split(flatten(B2:D7&"|"&A2:A7),"|")),2,0))

You can make the above formula further shortened by eliminating one of the ArrayFormula functions.

=ArrayFormula(vlookup({"Student 5";"Student 10";"Student 18"},split(flatten(B2:D7&"|"&A2:A7),"|"),2,0))

In the following example I’ve the search_keys as cell reference (F2:F4). It’s equal to the same above Vlookup formula.

Before concluding how to use Vlookup to find search key in multiple columns and return a value from the first column, one more thing to clarify.

We can use open ranges in the above formula. B2:D7 can be B2:D and A2:A7 can be A2:A. But when you change F2:F4 to F2:F, do include the IFNA outside the Vlookup but inside the ArrayFormula as below.

=ArrayFormula(ifna(vlookup(F2:F,split(flatten(B2:D&"|"&A2:A),"|"),2,0)))

There are plenty of rare Vlookup tutorials on this blog. Use the search icon on the navigation bar to find them.

I hope you could learn how to tune Vlookup to find a search key in multiple columns (matrix) in Google Sheets. Thanks for the stay. Enjoy!