VLOOKUP and HLOOKUP Combination in Google Sheets

Published on

The combination of VLOOKUP and HLOOKUP can work wonders in Google Sheets. In this technique, VLOOKUP searches the first column for a specified key, while HLOOKUP searches the first row for another key. The formula then returns the value at the intersection of these searches.

First, let’s clarify the concept. Below, I’ll demonstrate the purpose of the VLOOKUP and HLOOKUP combination in Google Sheets.

Purpose of VLOOKUP and HLOOKUP Combination

In this example, we have employee names in Column A and their monthly allowances from January to June in columns B to G.

The goal is to find the monthly allowance of a particular employee (given in cell J2) for a specific month (given in cell J3).

This can be achieved by combining VLOOKUP and HLOOKUP in two distinct ways.

Example of VLOOKUP and HLOOKUP combination in Google Sheets

Example of VLOOKUP and HLOOKUP Combination

When using this combination, one function can replace the index parameter in the other. Let’s start with VLOOKUP leading and HLOOKUP providing the dynamic index.

Approach 1: VLOOKUP Leading with HLOOKUP as Index

In the following example, the VLOOKUP formula finds the allowance of the employee “Tina Hill” for “Mar.” The formula searches the key in J2 (Tina Hill) in the first column of the range A1:G and returns the value from the fourth column, corresponding to “Mar.”

=VLOOKUP(J2, A1:G, 4, FALSE)

Here, 4 is the column index number for “Mar.”

Instead of manually specifying the column index (4), we can use an HLOOKUP formula inside VLOOKUP to dynamically determine the column index number for “Mar.”

The following HLOOKUP formula retrieves the column index:

=HLOOKUP(J3, {A1:G1; SEQUENCE(1, COLUMNS(A1:G1))}, 2, FALSE)

This formula searches for the key in J3 (Mar) in the first row of the range {A1:G1; SEQUENCE(1, COLUMNS(A1:G1))} and returns the value from the second row.

  • {A1:G1;SEQUENCE(1, COLUMNS(A1:G1))} is a constructed range where the first row contains the headers, and the second row contains their corresponding sequence numbers.

Now, we combine them:

=VLOOKUP(J2, A1:G, HLOOKUP(J3, {A1:G1; SEQUENCE(1, COLUMNS(A1:G1))}, 2, FALSE), FALSE)

In this combination, VLOOKUP performs the row search, and HLOOKUP dynamically provides the column index.

Approach 2: HLOOKUP Leading with VLOOKUP as Index

In this approach, HLOOKUP takes the lead. For example, the HLOOKUP formula below searches for the key in J3 (Mar) across the first row and returns the value from the fifth row:

=HLOOKUP(J3, A1:G, 5, FALSE)

Here, 5 is the row index number. Instead of manually specifying this index, we can use a VLOOKUP formula to dynamically retrieve it:

=VLOOKUP(J2, {A1:A, SEQUENCE(ROWS(A1:A))}, 2, FALSE)

This VLOOKUP formula generates sequence numbers for each row and returns the row index corresponding to the employee in J2.

Combining them results in the following formula:

=HLOOKUP(J3, A1:G, VLOOKUP(J2, {A1:A, SEQUENCE(ROWS(A1:A))}, 2, FALSE), FALSE)

Conclusion

You can use the VLOOKUP and HLOOKUP combination in two ways:

  1. VLOOKUP Leading: Use VLOOKUP to search the first column and HLOOKUP to dynamically return the column index.
  2. HLOOKUP Leading: Use HLOOKUP to search the first row and VLOOKUP to dynamically return the row index.

Both approaches yield the same result.

You can simplify the formulas by using the MATCH function to calculate the index. This approach is more versatile and efficient for two-way lookups. Refer to the related resources for detailed tutorials on advanced lookup techniques.

Resources

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.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

4 COMMENTS

  1. Hi,

    I’m trying to combine multiple conditions in an hlookup with a Vlookup.

    I have two rows that I need to pull different values from (hence multiple conditions with an hlookup), but I also want to Vlookup for a specific date.

    I’m getting the right results without adding the Vlookup into the formula, but once I add the Vlookup, I get an error that says “Vlookup evaluates to an out of bound range.”

    Can you help take a look at my formula in cell F3 to see what’s incorrect? Thank you!

    • Hi, Jeannie,

      This is your existing formula on the Sheet.

      =arrayformula(vlookup(E3,A:C,HLOOKUP("emailOrder Count",{$A$1:$C$1&$A$2:$C$2;$A3:$C3},2,0),0))

      See my new formula.

      =ArrayFormula(IFERROR(vlookup(E3:E,A3:C8,match("emailOrder Count",$A$1:$C$1&$A$2:$C$2,0),0)))

      You can easily find the difference. Also, see the image. I have keyed in this formula in cell F3 only.

      Array Use of Vlookup and Match combination

      • Hi Prashanth, thanks so much for the prompt response! Could you elaborate why match worked better in this case instead of hlookups?

        • Hi, Jeannie Wu,

          You want Vlookup to vertical lookup MULTIPLE SEARCH KEYS (E3:E), right?

          To do that, in Vlookup, you must find the Index column number dynamically. For this, you can either use the MATCH function or Hlookup.

          Match to return dynamic Index column:

          =ArrayFormula(match("emailOrder Count",$A$1:$C$1&$A$2:$C$2,0))

          Hlookup equivalent for returning dynamic index column:

          =ArrayFormula(hlookup("emailOrder Count",{($A$1:$C$1&$A$2:$C$2);column(A1:C1)},2,0))

          You have used this Hlookup formula wrongly earlier. That was the issue.

          Here are the formulas.

          Final Formula: Vlookup + Match:

          =ArrayFormula(IFERROR(vlookup(E3:E,A3:C8,match("emailOrder Count",$A$1:$C$1&$A$2:$C$2,0),0)))

          Note: In my earlier reply $A$1:$C$1&$A$2:$C$2 was wrongly entered as $B$1:$C$1&$A$2:$C$2 even though that was working for the current search key “emailOrder Count”. I have just updated my earlier comment to incorporate that correction.

          Equivalent Final Formula: Vlookup + Hlookup:

          =ArrayFormula(IFERROR(vlookup(E3:E,A3:C8,hlookup("emailOrder Count",{($A$1:$C$1&$A$2:$C$2);column(A1:C1)},2,0),0)))

          Hope this helps.

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.