Combine VLOOKUP & HLOOKUP, Multi Input, Single Output

Use a single lookup formula that combines the item and specification to return the size. A robust approach is to use INDEX with MATCH on a helper range that uniquely identifies each item/spec combination.

Assume:

• Column A: Item (input)
• Column B: Specification (input)
• A lookup table somewhere else with:
  • Column F: Item
  • Column G: Specification
  • Column H: Size

Then in Column C (Size output), use:

=INDEX($H$2:$H$100,
       MATCH(1,
             ($F$2:$F$100=A2)*($G$2:$G$100=B2),
             0))

Enter this as a dynamic array formula in newer Excel, or as a legacy array formula (Ctrl+Shift+Enter) in older versions.

Explanation:

• ($F$2:$F$100=A2) checks for matching item.
• ($G$2:$G$100=B2) checks for matching specification.
• Multiplying them creates a combined condition (both must be TRUE).
• MATCH(1, …, 0) finds the row where both conditions are met.
• INDEX returns the corresponding size.

This avoids needing to literally combine VLOOKUP and HLOOKUP and instead uses the recommended INDEX/MATCH pattern for multi-criteria lookups.

References:

• Use Excel built-in functions to find data in a table or a range of cells
• Look up values with VLOOKUP, INDEX, or MATCH
• VLOOKUP function
• HLOOKUP function
• Use the table_array argument in a lookup function
• How can I merge two or more tables?