Back to Formula Examples

VLOOKUP with Multiple Conditions

Learn how to create powerful lookups with multiple criteria using INDEX-MATCH formulas, with help from ExcelGPT.

Understanding the Problem

Standard VLOOKUP in Excel can only search based on a single criterion in the leftmost column. When you need to match data based on multiple conditions, you need a more powerful approach using INDEX and MATCH functions together.

The Formula Solution

=IFERROR(INDEX(C:C,MATCH(1,(A:A=A2)*(B:B=B2),0)),"Not Available")

This formula works by:

  • INDEX(C:C, ...) - Returns a value from column C based on the row number provided by MATCH
  • (A:A=A2)*(B:B=B2) - Creates arrays of TRUE/FALSE values (converted to 1/0) for each condition
  • MATCH(1, ..., 0) - Finds the position where both conditions are TRUE (multiplied to 1)
  • IFERROR(..., "Not Available") - Returns a friendly message if no match is found

Note: This formula must be entered as an array formula in older Excel versions (Ctrl+Shift+Enter). In Excel 365 and newer versions, it works automatically.

Example Use Case

Imagine you have a product database with columns for Product Category, Product ID, and Price. You want to look up the price based on both the category and ID.

This is a placeholder for an interactive example of the INDEX-MATCH formula for multiple criteria lookups.

Future implementation will include an interactive Excel-like interface to try this formula with sample data.

How ExcelGPT Can Help

ExcelGPT can assist you with creating complex lookup formulas by:

  • Analyzing your data structure and recommending the most efficient lookup approach
  • Generating the exact formula syntax based on your column references
  • Explaining how the formula works so you can modify it for your needs
  • Troubleshooting issues when your lookup formula isn't returning expected results
    v1.0.0