Go Back

Learn How to Use VLOOKUP and HLOOKUP Together in Excel

We can use a nested formula that combines the VLOOKUP and HLOOKUP Functions in excel to retrieve values from a table. Approximate and exact matching is supported by this combined formula and wildcards (* ?) are for finding partial matches. The steps below will walk through the process.

Figure 1- How to Use VLOOKUP and HLOOKUP Together in Excel


=VLOOKUP(lookup_value,lookup_array,HLOOKUP(lookup_value ,lookup_array,2,range_lookup),range_lookup)

  • Lookup_Value: This is the value to search for
  • Lookup_array: This is the range to search for the lookup value
  • HLOOKUP: This serves as the COLUMN NUMBER in the VLOOKUP formula. It specifies the COLUMN where we retrieve the data
  • Range_lookup: This is used to specify if we want an approximate or exact match. If omitted, an approximate match is used



Setting up the Data

  • We will use the combined formula to find the earning of California in 2016.

Figure 2 – Setting up the Data

Using the VLOOKUP and HLOOKUP Functions

  • We will click on Cell G5
  • We will insert the formula below into Cell G5
  • We will press the enter key

Figure 3- Result of the VLOOKUP and HLOOKUP Functions


In this formula, the horizontal value (2016) is looked up with the HLOOKUP section of the formula. The returned value by HLOOKUP is 5 because HLOOKUP searches for 2016 in the range B3:E4 in ROW 2.

The vertical lookup value (California) is searched for by VLOOKUP. VLOOKUP searches the range A5:E10 for what is contained in Cell G3. After identifying California in the range, it then matches California with the returned column value (5) by HLOOKUP and returns $141,000.


  • If the lookup_value is greater than every value in the lookup table, the formula returns with the last value provided it has been set to approximate match
  • If the lookup_value is less than all values in the lookup table, the function returns the #N/A error
  • If we omit range_lookup, both functions will allow a non-exact or approximate match, but it will use an exact match if one exists. The approximate match is set as 1 and exact match is set as 2.
  • If we set the range_lookup to approximate match, then the lookup values in the first row of the table must be sorted in ascending order. Otherwise, HLOOKUP may return an incorrect or unexpected value.
  • If range_lookup is FALSE (exact match), values in the first row of the table do not need to be sorted.

This formula can only be used when the data is in a matrix form.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment