Table of Contents
HLOOKUP and VLOOKUP are two powerful functions in Excel that allow users to search for specific data in a table or range of cells. HLOOKUP (Horizontal Lookup) searches for a value in the first row of a range and returns a corresponding value from a specified row. VLOOKUP (Vertical Lookup) works in a similar way, but searches for a value in the first column of a range and returns a corresponding value from a specified column.
These functions can be used together to efficiently retrieve data from a table or range that has both horizontal and vertical elements. By utilizing both functions, users can search for a value in both the first row and first column of a range, allowing for more precise and accurate data retrieval. This can be useful in situations such as sales reports, inventory tracking, and financial analysis. Furthermore, HLOOKUP and VLOOKUP can be combined with other functions and formulas to perform more complex tasks, making them essential tools for data analysis and management in Excel.
Excel: Use HLOOKUP and VLOOKUP Together
You can use the HLOOKUP and VLOOKUP functions in Excel to look up values both horizontally and vertically in a dataset and return the corresponding matching value.
You can use the following basic syntax to do so:
=VLOOKUP(B11,A3:E8,HLOOKUP(B12,B1:E2,2,FALSE),FALSE)
This particular formula looks up the value in cell B11 in column A of the vertical range A3:E8, then looks up the value in cell B12 in row 1 of the horizontal range B1:E2 and returns the corresponding cell value where these two lookup values intersect.
The following example shows how to use this syntax in practice.
Example: Use HLOOKUP and VLOOKUP Together in Excel
Suppose we have the following dataset in Excel that shows the sales made at various retail stores for some company during each quarter:
Note: It’s necessary to create the row called Column Num that lists the Excel column numbers so that the formula will return the correct value.
Suppose we would like to look up the sales value for the West region during Quarter 2.
We can type the following formula into cell B13 to extract this value:
=VLOOKUP(B11,A3:E8,HLOOKUP(B12,B1:E2,2,FALSE),FALSE)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 25, which corresponds to the sales value for the West region during Quarter 2.
If we change the values for the region and quarter, the formula will update the sales value accordingly.
For example, suppose we change the region to Pacific and the quarter to Quarter 4:
The formula returns a value of 40, which corresponds to the sales value for the Pacific region during Quarter 4.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can HLOOKUP and VLOOKUP be used together in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-hlookup-and-vlookup-be-used-together-in-excel/
stats writer. "How can HLOOKUP and VLOOKUP be used together in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-hlookup-and-vlookup-be-used-together-in-excel/.
stats writer. "How can HLOOKUP and VLOOKUP be used together in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-hlookup-and-vlookup-be-used-together-in-excel/.
stats writer (2024) 'How can HLOOKUP and VLOOKUP be used together in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-hlookup-and-vlookup-be-used-together-in-excel/.
[1] stats writer, "How can HLOOKUP and VLOOKUP be used together in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can HLOOKUP and VLOOKUP be used together in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.