VLOOKUP & HLOOKUP Combined – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2023
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


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(lookup_value, table_array, col_index_num, [range_lookup])

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

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List