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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Compare Two Lists Using VLOOKUP
Excel: Use VLOOKUP to Return Multiple Values Horizontally
Excel: How to Use VLOOKUP by Date
