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