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
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:
- VLOOKUP Leading: Use VLOOKUP to search the first column and HLOOKUP to dynamically return the column index.
- 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.
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.
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$2was wrongly entered as$B$1:$C$1&$A$2:$C$2even 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.