Two-way Lookup Using VLOOKUP in Google Sheets [Normal and Array]

In its basic form, performing a two-way lookup using VLOOKUP in Google Sheets isn’t possible without combining functions.

The recommended functions to use with VLOOKUP are MATCH or HLOOKUP.

A two-way lookup means looking up one search key in the first column and another search key in the first row of a table, and returning the intersecting value.

In Google Sheets, to make a two-way lookup using VLOOKUP work, you need to combine either of the functions mentioned above to create a powerful combination.

Let me explain this more clearly.

Syntax of the Normal One-Way Lookup Formula – Vertical Lookup

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

Syntax of the Two-Way Lookup Formula – The Combination of Vertical and Horizontal Lookup

There are two main options, with the first being a combination of VLOOKUP and MATCH:

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

You can also combine VLOOKUP and HLOOKUP for a two-way lookup in Google Sheets:

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

Which One is Better?

If you’re wondering which combination is better, both the VLOOKUP-MATCH and VLOOKUP-HLOOKUP combinations are almost equally effective in performing a two-way lookup. Both can handle vertical and horizontal lookups together.

Additionally, both combinations support array results (multiple search keys).

This tutorial will focus on the VLOOKUP and MATCH combination for performing a two-way lookup.

For the VLOOKUP and HLOOKUP combination, refer to this guide: VLOOKUP and HLOOKUP Combination in Google Sheets.

Two-Way Lookup Using VLOOKUP in Google Sheets

You can use multiple search keys in a two-way lookup.

In the first example below, I will demonstrate a simple two-way lookup with one search key in the first column (vertical lookup) and another search key in the first row (horizontal lookup).

This is a non-array formula.

Two-Way Lookup in Google Sheets – Non-Array Formula

Example:

Two-way lookup using VLOOKUP in Google Sheets with a single search key

I hope this illustration can clarify how a two-way lookup works.

This sample data shows student marks in various subjects. The first column contains subjects, while the other columns contain marks for different students.

Formula #1: Normal Two-Way Lookup Formula in Google Sheets

In my example, I have used the following formula in cell H3 to return the marks of “Student 2” in the “Physics” subject, which is 76:

=IFERROR(VLOOKUP(F3, A2:D, MATCH(G3, A2:D2, 0), 0))

How Does This Two-Way Lookup Using VLOOKUP Work?

Let’s break it down with the following VLOOKUP formula:

=IFERROR(VLOOKUP(F3, A2:D, 3, 0))

This formula is equivalent to Formula #1.

The column index #3 in this formula refers to the third column in the data range, which contains the marks for “Student 2.”

The key difference here is that the MATCH function in Formula #1 returns the number 3 (the relative position) by searching for the keyword, in this case, “Student 2.”

Two-Way Lookup in Google Sheets – Array Formula

Now, let’s look at how to handle multiple search keys in a two-way lookup using the VLOOKUP and MATCH combination.

Example:

Two-way lookup using VLOOKUP in Google Sheets with multiple search keys

Here is the array formula for two-way lookup, as seen in the above example.

Formula #2: Array-Based Two-Way Lookup Formula in Google Sheets

=ArrayFormula(IFERROR(VLOOKUP(F3:F, A2:D, MATCH(G3:G, A2:D2, 0), 0)))

This is similar to Formula #1, but the main difference is that we’ve included the range that contains the search keys. Specifically, we’ve replaced F3 with F3:F and G3 with G3:G.

Additionally, the formula is wrapped in ArrayFormula to return multiple results.

Additional Lookup Tips for the Geeks

Earlier in this tutorial, I mentioned using HLOOKUP instead of MATCH.

Yes! Two-way lookup in Google Sheets is also possible with the VLOOKUP and HLOOKUP combination.

This combination can be used in both non-array and array forms.

For example, the following formula can replace the array formula #2 above:

=ArrayFormula(IFERROR(VLOOKUP(F3:F, A2:D, HLOOKUP(G3:G, {A2:D2; SEQUENCE(1, COLUMNS(A2:D2))}, 2, 0), 0)))

Conclusion

I’ve provided the best available formula combinations for performing a two-way lookup using VLOOKUP in Google Sheets.

If you have any questions about the formulas discussed in this post, feel free to ask in the comments.

I’ll do my best to simplify the steps for you.

Thanks for reading, and I hope you enjoyed your stay!

19 COMMENTS

Ranjana July 31, 2023 At 2:45 pm

Hi,

Thank you for the article. There is a big dataset I am working on wherein I am making a dashboard for 2-wheeler monthly sales across different states and cities.

In the dashboard, I have a dropdown menu for selecting the state, and I would like to showcase a plot for city sales in that particular state.

To achieve that, I need a dynamic helper table that updates on month and state selection. Can you please help me with that?

- Prashanth July 31, 2023 At 7:01 pm
  
  Hi Ranjana,
  
  I’d be happy to help you with this. Could you please share a sample Sheet URL in the comments so that I can take a look?
  
  - Ranjana August 1, 2023 At 10:16 am
    
    Dear Prashanth,
    
    Thank you for your quick response. I was working on it yesterday and was able to solve it, but I still feel like there has to be an easier or quicker way to use smart formulas.
    
    Here is the link to the sheet I copied: [URL removed]
    
    Could you take a look and see if there’s a way to simplify the formula?
    
    Thank you so much again for your support!
    
    - Prashanth August 1, 2023 At 11:57 am
      
      Hi Ranjana,
      
      I used QUERY to solve it instead of a Pivot Table workaround. Please see your sheet for the formula.
      
      - Ranjana August 1, 2023 At 2:07 pm
        
        Hi Prashanth,
        
        Thank you so much for your help. The code you provided works great, but it only displays the total sales. Is there a way to modify it to show the sales for the month selected in the dashboard?
        
        
        Prashanth August 1, 2023 At 6:28 pm
        
        I’ve updated the formula. Give that a try.
        
      - Ranjana August 2, 2023 At 3:45 pm
        
        This was very helpful. It works perfectly!
        Thank you so much.
        
Makaro May 6, 2023 At 9:35 pm

Thank you very much.

Can you please help me,

From your table, I need a formula to Lookup the Subject.

Example:

Question: what is the Subject with value 76 owned by “Student 2”?

Answer: “Physics”

I need a Google Sheets formula that returns the result, i.e., “Physics.”

Please email me the answer. Thank you.

- Prashanth May 10, 2023 At 8:46 am
  
  Hi, Makaro,
  
  You may please try this.
  
  =ifna(let(table,A2:D,score,78,student,"Student 1", index(choosecols(table,1), match(score,choosecols(table, match(student,chooserows(table,1),0)),0))),"No Match!")
  
  - Makaro May 10, 2023 At 8:57 pm
    
    Wow, it works. Thank you so much for the answer.
    
AnthonyOusley September 15, 2021 At 9:26 pm

Thanks! I tried it but getting a “#N/A” Error still. Can you please log in to this and see what I am doing wrong?

- Prashanth September 18, 2021 At 9:35 am
  
  Hi, AnthonyOusley,
  
  Sure. Please feel free to share your sheet or copy of a sample sheet filled with dummy data.
  I won’t publish your comment containing the URL.
  
Anthony Ousley September 10, 2021 At 3:42 am

I need someone’s help with this.

I need help with Vlookup and between 2 dates. I got it returning the correct highest number.

But now I need it to return the name of the salesman who had “highest gross” and was “between these two dates.”

Can someone please help me with this? Here is the link to the sheet: — LINK REMOVED BY ADMIN —

Look at Page “NEW MONTHLY TOTALS” on cell #J64, and this is where I am trying to get the “Name” of the person who had “HIGHEST USED FRONTEND GROSS” from the “PRE-OWNED VEHICLES (in cells M6:M155)” Page.

The cells that have the “Date of Sale” are on “PRE-OWNED VEHICLES (in cells B6:B155)” but must be between the date range “NEW MONTHLY TOTALS cells between C62 and F62.”

- Prashanth September 11, 2021 At 11:31 am
  
  Hi, Anthony Ousley,
  
  You can try this formula in cell J64.
  
  =vlookup( H64, filter( {'PRE-OWNED VEHICLES'!M6:M155,'PRE-OWNED VEHICLES'!D6:D155}, isbetween('PRE-OWNED VEHICLES'!B6:B155,'NEW MONTHLY TOTALS'!C62,'NEW MONTHLY TOTALS'!F62) ),2,0 )
  
  FILTER and ISBETWEEN are the functions used other than VLOOKUP.
  
Jim Spalding August 30, 2021 At 5:20 pm

Thank you very much. I learned something new with this formula that I had never used IFNA. Thank you again.

Jim Spalding August 27, 2021 At 5:51 pm

My company does not allow sharing outside the company.

So I have slimmed down what I have and copied it into my google account. Here is the link.

— link removed by the admin —

I appreciate the help on this.

- Prashanth August 28, 2021 At 12:23 pm
  
  Hi, Jim Spalding,
  
  I made copies of your tabs, and they are “Copy of Delivery” and “Copy of Schedule.”
  
  Then I have modified your existing data in these copied sheets for testing.
  
  You can find the formula in cell P3 in “Copy of Schedule.”
  
Jim Spalding August 27, 2021 At 1:24 am

I need some assistance in doing something similar, and I have struggled to make it work.
I am trying to pull data from one tab into another for the delivery schedule of parts into a production schedule that has this part as a component.
I want to see how many are being delivered for that day, and it looks out five days.
It is a table in which part numbers are in column A, dates are in row 1, with the quantity for each part at the intersection of the row and column.
The destination sheet has five columns representing the days of the week with the date as header, and the part number is in the rows.
I have an example google sheet I can share.

- Prashanth August 27, 2021 At 10:53 am
  
  Hi, Jim Spalding,
  
  It seems doable.
  Leave the URL/link of your sheet in your reply below.
  I’ll keep it away from public view.