In the real sense, two-way lookup using Vlookup in Google Sheets is not possible without making a combination formula.
The recommended functions to use with Vlookup are Match or Hlookup.
Two-way lookup means to lookup one search key in the first column and another search key in the first row of a table and returns the intersecting value.
In Google Sheets, to make the two-way lookup using Vlookup works, you should use either of the above-said functions to form a powerful combination.
Let me explain it more clearly.
Here is the Syntax of the normal one-way Lookup formula – Vertical Lookup
VLOOKUP(search_key, range, index, [is_sorted])The Syntax of Two-way Lookup formula – The combination of Vertical and Horizontal Lookup
Here there are two options and the first one is using the functions Vlookup and Match.
VLOOKUP(search_key, range, Match, [is_sorted])No doubt, you can combine Vlookup and Hlookup too for two-way lookup in Google Sheets.
VLOOKUP(search_key, range, Hlookup, [is_sorted])Must Check: Learn All Popular Google Sheets Functions
Can you tell me which one is better?
If this is your question, no doubt, for me, both are almost the same in usability. Any of the above combinations can do vertical and horizontal lookup together.
Also, with both of these combinations, array results (multiple search keys) are possible.
This Google Sheets tutorial is about the Vlookup and Match combination for two-way lookup.
Regarding the Vlookup and Hlookup combination, please refer to this guide – Vlookup and Hlookup Combination In Google Sheets.
Two-way Lookup Using Vlookup in Google Sheets
You can use multiple keywords in this type of two-way lookup.
In the first example below, I will use only one/single keyword each, that in the first column (vertical lookup) and first row (horizontal lookup).
That means it’s a non-array formula.
Two-way Lookup in Google Sheets – Non-Array Formula
Example:-
I hope this picture or infographic can convey everything that I want to say about two-way lookups.
Formula # 1: Normal Two-way lookup formula in Google Sheets.
In my above example, I have inserted the following formula in cell H3, which returns the mark of “Student 2” in the Subject Physics, which is 76.
=IFERROR(VLOOKUP(F3,A2:D,MATCH(G3,A2:D2,0),0))Do you know how this two-way lookup using Vlookup in Google Sheets works?
To understand this see the below Vlookup formula.
=IFERROR(VLOOKUP(F3,A2:D,3,0))It is equal to the above formula # 1!
Column Index # 3 in this formula refers to the third column in the data range containing the marks of “Student 2.”
Here is the difference.
The MATCH formula in formula # 1 returns this number 3 (relative position) by searching the keyword, i.e., “Student 2”.
Two-way Lookup in Google Sheets – Array Formula
Let’s see how to deal with multiple search keys in two-way lookup using Vlookup and Match combination formula.
Example:-
Here is the two-way lookup array formula, which you can see 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)))It is not much different from formula # 1.
Just include the range that contains the keywords. I mean, replace F3 with F3: F and G3 with G3: G.
Additionally, wrap the formula with the ArrayFormula for expanding the results.
Additional Lookup Tips for the Geeks
At the beginning of this Google Spreadsheet tutorial, I have mentioned using Hlookup instead of the Match function.
Yes! Two-way lookup in Google Sheets is also possible with the Vlookup and Hlookup combination.
You can use this new combination formula also in non-array or array form.
For example, this formula can replace the array formula #2 above.
=ArrayFormula(IFERROR(VLOOKUP(F3:F,A2:D,HLOOKUP(G3:G,{A2:D2;COLUMN(A2:D2)},2,0),0)))Conclusion
I have provided the best available formula combinations for two-way lookup using Vlookup in Google Sheets.
If you have any doubts regarding the formulas detailed in this post, you may feel free to ask me in the comments.
I’ll try to simplify the steps for you.
Thanks, and I hope you have enjoyed the stay!
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?
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?
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!
Hi Ranjana,
I used QUERY to solve it instead of a Pivot Table workaround. Please see your sheet for the formula.
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?
I’ve updated the formula. Give that a try.
This was very helpful. It works perfectly!
Thank you so much.
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.
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!")
Wow, it works. Thank you so much for the answer.
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?
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.
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.”
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.
Thank you very much. I learned something new with this formula that I had never used IFNA. Thank you again.
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.
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.”
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.
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.