Retrieving Sale Price Based On Item And Color Criteria in Microsoft Excel 2010

In this article we will learn about how we can recover sale price based on item and color criteria in Microsoft Excel 2010

To retrieve sale price based on item and color criteria, we will use a combination of "INDEX" & "MATCH" functions to get the output.
"INDEX": Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Syntax: =INDEX(array,row_num,column_num)
"MATCH" function searches for a specified item in a selected range of cells, and then returns the relative position of that item in the range.

Syntax =MATCH(lookup_value,lookup_array,match_type)

Let us take an example:-

We have list of items in column A, color of the item in column B and price of the item in column C.We need a formula to find the matching price for each pair of item and color values entered in A9:B12.

• The formula in cell C9 would be
• =INDEX($C$2:$C$5,MATCH(A9&B9,$A$2:$A$5&$B$2:$B$5,0))}
• This is an array formula, after entered the formula press Ctrl+Shift+Enter on your keyboard.
• Formula will look like this :- {=INDEX($C$2:$C$5,MATCH(A9&B9,$A$2:$A$5&$B$2:$B$5,0))}

• Copy down the formula from cell C9 to range C10:C12.While copying, you need to select cell C9 first then select range C10:C12 & then paste the formula as this is an array formula, and we will get the desired result.

This is the way we can regain the sale price based on item and color criteria in Microsoft Excel.