Problem:
Range A2:C7 contains a list of items and their corresponding colors and prices.
How could we find the name of the first Blue item which costs less than $200?
Solution:
Enter the following array formula, using the INDEX and MATCH functions:
{=INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7
Example:
Item______Color____Price___Criteria
Shoes_____Red______$140____Blue
Jacket____Brown____$300____$200
Shoes_____Blue_____$199
T-Shirt___Green____$10
Jeans_____Blue_____$150
Shoes_____Black____$99
Result_____Shoes
Bookmarks