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