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.

img1

  • 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))}

img2

  • 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.

img3

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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube