• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Multiple Condition Lookup Formula in Excel

Multiple Condition Lookup Formula in Excel

You may require a multiple condition lookup formula in Excel to perform a lookup based on the values in multiple columns. For example, in the image below the formula in cell B3 returns the code based on the country and area specified in cells B1 and B2.

Multiple condition lookup formula returning an area based on two values

Lookup a Value using Multiple Conditions

This example uses an array formula meaning that instead of pressing Enter to run the formula, you should press Ctrl + Shift + Enter. You do not type the curly braces shown in the formula.

The INDEX and MATCH functions are used for this lookup formula (Learn about INDEX and MATCH).

The formula below will return the code if the country matches the one entered in cell B1, and the area matches the one entered in cell B2. This examples uses two conditions. You can include as many conditions as you need using the same technique describe in this tutorial.

Multiple condition lookup formula using INDEX and MATCH

The two conditions are entered inside brackets within the lookup_array argument of the MATCH function. The * symbol has been used to apply the AND logic between the two conditions. The + symbol can be used to apply the OR logic.

The result of the two tests will be returned as a 1 for TRUE, or a 0 for FALSE. The MATCH function uses a 1 for its lookup_value so that it returns the row number for the record where both conditions are met.

Watch the Video – Multiple Condition Lookup Formula

Related Posts:

  • Case sensitive XLOOKUP feature
    Case Sensitive Lookup with XLOOKUP in Excel
  • Calculate age in Excel thumbnail
    How to Calculate Age in Excel
  • Sum formulas only in Excel
    Sum Formulas Only in Excel
  • IMAGE function in Excel
    Excel IMAGE Function - Insert Images from a Cell Value

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Recent Posts

  • Excel SWITCH Function – Nested IF Function Alternative
  • Sum Formulas Only in Excel
  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Blog
  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·