VLOOKUP & HLOOKUP Combined – Excel & Google Sheets
Download the example workbook
This tutorial will demonstrate how to use the VLOOKUP and HLOOKUP Functions together to lookup a value in Excel and Google Sheets.
Combine VLOOKUP and HLOOKUP
Sometimes you need the column reference of your VLOOKUP formula to be dynamic. Usually, the best way to do this is by combining the VLOOKUP & MATCH Functions, but in this tutorial we will demonstrate the VLOOKUP-HLOOKUP combination instead.
In this example, our formula returns the amount of Copper in Asia, given the continent and the metal.
Let’s see how to combine these two popular functions.
First add a helper row (Row 3 below) with the column numbers. Then use the HLOOKUP Function to return the column number from that row for use in the VLOOKUP Function.
Once you have the column number, you can use it in your VLOOKUP formula as the col_index_num input.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Putting these formulas together, yields our original formula.
VLOOKUP & HLOOKUP Combined in Google Sheets
All the examples explained above work the same in Google sheets as they do in Excel.