Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles LOOKUP Learn How to Use VLOOKUP and HLOOKUP Together in Excel

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

Syntax

=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

Formula

=VLOOKUP(G3,A5:E10,HLOOKUP(G4,B3:E4,2,FALSE),FALSE)

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
    =VLOOKUP(G3,A5:E10,HLOOKUP(G4,B3:E4,2,FALSE),FALSE)
  • We will press the enter key

Figure 3- Result of the VLOOKUP and HLOOKUP Functions

Explanation

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.

Note

  • 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.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc