Tip Printed from ExcelTip.com

---

Retrieving Sale Price Based On Item And Color Criteria


Problem:

Cells A1:C5 contain color and price data for a range of items.
We want to find the matching price for each pair of item and color values entered in A9:B11.

Solution:
Use the INDEX and MATCH functions as shown in the following Array formula:
{=INDEX($C$2:$C$5,MATCH(1,($A$2:$A$5=A9)*($B$2:$B$5=B9),0))}

Example:

Range1

Item________Color________Price
Sneakers____Red__________$70
Jeans_______Blue_________$50
T-Shirt_____Yellow_______#N/A


Range2

Item________Color______Price
Jeans_______Blue_______$50
Sneakers____Red________$70
T-Shirt_____Green______$10
Skirt_______Orange_____$30