Yes. You can nest VLOOKUP inside HLOOKUP so VLOOKUP finds the row number and HLOOKUP finds the column heading; the formula can then return the value at their intersection. This works, but it usually needs a helper table, so a two-way INDEX/MATCH formula is often easier to maintain.
What combining the two functions means
VLOOKUP searches down the first column of a range and returns a value from a specified column in the matching row. HLOOKUP searches across the first row of a range and returns a value from a specified row in the matching column. Each function is designed for one direction, but one can supply an argument needed by the other.
In the example below, HLOOKUP searches for a month, while a nested VLOOKUP looks up a product and returns the row number HLOOKUP needs. The functions are not automatically coordinating two searches: the helper table tells VLOOKUP which row number corresponds to each product. The number must be relative to the HLOOKUP range, not the worksheet row number.
Set up a concrete example
Suppose B2:E5 contains a small sales table. The month headings are in B2:E2, and each following row has a product’s sales for those months:
- Row 2: Jan, Feb, Mar
- Row 3: Mug, with sales of 400, 450, and 500
- Row 4: Tote, with sales of 700, 900, and 950
- Row 5: Notebook, with sales of 300, 350, and 425
Put the product to find in A8 and the month to find in B8. For this example, A8 contains Tote and B8 contains Feb. Create a helper table in K2:L4 with product names in K and their row positions in L: Mug is 2, Tote is 3, and Notebook is 4. These positions count from the top of the HLOOKUP range, B2:E5: the header row is 1, so the first product row is 2.
Use the nested formula
With month headings in B2:E2, sales data in B3:E5, Tote in A8, Feb in B8, and the product-to-row map in K2:L4, enter this formula in the result cell:
=HLOOKUP(B8,$B$2:$E$5,VLOOKUP(A8,$K$2:$L$4,2,FALSE),FALSE)
The inner VLOOKUP searches for Tote in the first column of K2:L4, returns the value from its second column, and produces 3. The outer HLOOKUP searches for Feb in the first row of B2:E5. It then returns the value from the third row of that range, which is the Tote row. The result is 900.
Both final arguments are FALSE, which requests an exact match. That matters for text labels such as product names and month names: an approximate match could return a misleading result if the lookup data is not arranged for approximate matching.
Build and check the formula step by step
- Arrange the data so the month labels are in the top row of the HLOOKUP range and the sales values are beneath them.
- Make a helper table that lists each product and the corresponding row number within that range. Include the header row when counting positions.
- Enter the requested product and month in the input cells.
- Insert the VLOOKUP inside HLOOKUP’s row_index_num argument, as in the example. Lock the lookup ranges with dollar signs so they stay fixed if you copy the formula.
- Check the helper number manually. For example, a value of 3 must point to the third row of B2:E5, not worksheet row 3.
If you copy the formula to other result cells, the absolute references keep the source ranges in place, while A8 and B8 can change to refer to the new inputs. If you want to fill the formula down or across, check that the input references move in the direction you expect.
Why the helper table matters
HLOOKUP’s third argument is a row number, not a product name. VLOOKUP can turn a product name into that number, but Excel does not infer the mapping from the layout. The helper table must stay synchronized with the data table: if rows are reordered, or products are added, update the mapping too. A wrong mapping can return a valid-looking number from the wrong product row.
This is also why nesting these functions is not usually the simplest way to create a two-way lookup. It is useful when the worksheet already has a row-position map or when the formula must specifically use HLOOKUP and VLOOKUP. For a new worksheet, a formula that searches the row and column labels directly is generally less fragile.
A simpler two-way lookup with INDEX and MATCH
For a more conventional layout, put product names in A3:A5, month headings in B2:E2, and the corresponding values in B3:E5. With Tote in A8 and Feb in B8, use:
=INDEX($B$3:$E$5,MATCH(A8,$A$3:$A$5,0),MATCH(B8,$B$2:$E$2,0))
The first MATCH finds Tote’s position among the product names, and the second finds Feb’s position among the month headings. INDEX returns the value at those row and column positions. In the example, the result is 900. The 0 in each MATCH requests an exact match.
This approach avoids storing row numbers in a separate helper table. It also makes the direction of each search explicit. INDEX and MATCH are available in older Excel versions as well as Microsoft 365, so this is a practical option when a workbook needs to work across a broad range of versions.
Common errors and ways to prevent them
- #N/A: One of the lookup values may not exactly match its label in the table. Check spelling, extra spaces, and whether a date or number is stored in a different form. Confirm that both function arguments use FALSE for exact matching.
- #REF!: The row number returned by VLOOKUP may be larger than the number of rows in the HLOOKUP range, or a referenced range may have been deleted. Check that the helper values run from 2 through the number of product rows plus 1.
- Wrong value, but no error: A helper row number may point to a different product. Verify the count relative to the first row of the HLOOKUP range, including its header row.
- Unexpected approximate results: If the final FALSE argument is omitted, HLOOKUP and VLOOKUP default to approximate matching. Keep FALSE for exact text, date, or identifier lookups unless approximate matching is deliberately required and the data is set up for it.
- Formula changes after editing: Dollar signs lock ranges when copying, but they do not make a manually maintained row map self-updating in every rearrangement. Recheck the helper table after inserting, deleting, or sorting rows.
When a result needs a readable fallback instead of an error, you can wrap the lookup in IFERROR, but choose the fallback carefully: hiding an error can also hide a misspelled lookup value or a broken reference. During setup, it is often better to see the error and fix the underlying mismatch.
How these functions compare
VLOOKUP is a natural fit when the lookup key is in the leftmost column and the answer is to its right. HLOOKUP is a natural fit when the lookup key is in the top row and the answer is below it. The nested example uses both orientations, but its helper map adds maintenance work. Neither function is inherently a general-purpose two-way lookup on its own.
A common misconception is that combining HLOOKUP and VLOOKUP is required whenever a table has both row and column headings. It is not. INDEX and MATCH can locate both headings directly, while newer Excel versions also offer XLOOKUP-based approaches. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not necessary for the examples here. Choose based on the workbook’s version requirements, layout, and how easily the formula can be checked by someone else.
Frequently Asked Questions
Can I put VLOOKUP inside HLOOKUP? Yes. VLOOKUP can supply HLOOKUP’s row_index_num argument, as long as its returned number is a valid row position within the HLOOKUP table range. Use exact matches when looking up labels.
Can I put HLOOKUP inside VLOOKUP? Yes, if HLOOKUP returns a valid column number for VLOOKUP’s col_index_num argument. The same principle applies: the number must be counted from the first column of the VLOOKUP range. In many workbooks, a direct INDEX/MATCH formula is clearer than adding another helper map.
Why does my combined formula return #N/A? At least one lookup may not find an exact match. Compare the input with the labels in the relevant first column or top row, and look for extra spaces or inconsistent data types. Also confirm that the nested VLOOKUP’s lookup table starts with the product names.
Does the helper row number start at 1? It starts at 1 for the first row of the HLOOKUP table array. If that first row contains the month headings, the first product row is 2. These are positions within the selected range, not worksheet row numbers.
Is INDEX and MATCH better for a two-way lookup? Often, yes, because it can find the product and month positions directly without a separate row-number map. It is not automatically best for every workbook, but it is usually easier to maintain when the table’s rows may change.
Choosing the right approach
Use nested HLOOKUP and VLOOKUP when you have a clear reason to use both functions and can maintain the helper mapping. Use INDEX and MATCH when you want a straightforward two-way lookup that reads the row and column labels directly. Whichever method you choose, use exact matching for labels, anchor ranges where appropriate, and test a known product-month combination so you can confirm both the lookup positions and the returned value.
Related
- Correlation Table in Excel: Create and Read One
- Python Pandas Excel Export: Methods and Examples
- CSV File to Excel Converter: Convert Without Data Loss
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MID String: How to Extract Text by Position in CellsIn Excel, use the MID function to extract a specified number of characters from the middle of a…
- SUMIF Greater Than in Excel: Criteria and ExamplesTo use SUMIF in Excel for values greater than a number, put the comparison in quotation marks as…
- Find Month and Year From a Date in Excel with FormulasTo find a month and year from a date in Excel, enter =MONTH(A2) for the month number and…
- PMT Function Negative in Excel? The Sign Rule ExplainedYour PMT function is usually negative because Excel treats the loan amount and its repayments as cash flows…
- Excel INDEX Absolute References: When and How to Use ThemYes, an Excel INDEX formula can use absolute references, and locking its ranges is often helpful when you…
- Excel Formula for Splitting Text in a Cell by DelimiterIn Microsoft 365 or Excel 2024, use TEXTSPLIT to divide text from one cell into separate cells. If…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.