» Retrieving an Item from a List that Meets Multiple Criteria

CATEGORY - Lookup Formulas VERSION - All Microsoft Excel Versions

Problem:

Range A2:C7 contains a list of items and their corresponding colors and prices.
We want to find the name of the first Blue item that costs less than $200.

Solution:

Use the INDEX and MATCH functions as shown in the following Array formula:
{=INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7