Return to Excel Formulas List

VLOOKUP & HLOOKUP Combined – Excel & Google Sheets

Download Example Workbook

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.

vlookup hlookup combined Main

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.

vlookup hlookup combined 01

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.

HLOOKUP Function

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.

vlookup hlookup combined 02

VLOOKUP Function

Once you have the column number, you can use it in your VLOOKUP formula as the col_index_num input.

vlookup hlookup combined 03

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.

vlookup hlookup combined Google