Retrieving a Price from a List that Matches both Category and Item Criteria

To retrieve a price from a list that matches both category and item criteria in Excel, 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)

array:Array is a range of cells or table.

row_num:The row number in the array from which the value is returned.

column_num:It is optional. It is the column number in the array that is used to return the value.

 
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)

lookup_value: The value you want to look for

lookup_array: The table of data contains information from which you want to return the output.

match_type: 1,0 and -1 are three options.

1(Default): It will find the largest value in the range. List must be sorted in ascending order.

0: It will find an exact match

-1: It will find the smallest value in the range. List must be sorted in descending order.

 
Let us take an example:

We have electronic items in column A, category in column B & their respective price in column C.Column E & F contain matching item & category.We need a formula to retrieve the price from column C by matching both the criteria from column E & F.

 
img1
 

  • In cell G2, the formula would be
  • {=INDEX($C$2:$C$11,MATCH(E2&F2,$A$2:$A$11&$B$2:$B$11,0))}
  • This is an array formula which requires formula to be enclosed with curly brackets by using CTRL + SHIFT + ENTER.

 
img2
 

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

 
img3
 
This is the way we can retrieve a price from a list that matches both category and item criteria in Microsoft Excel.
 
 

Users are saying about us...

  1. Here, I want if I enter latest price of laptop A in A2 then in G2 there's automatically updated the new price.Like if I enter in A2 the price of Laptop A 500 so in G2 excel should automatically update the new price .. Is it possible....??

    • Simply add the sheet name with ! before the range and it will be done. for example, if your data in sheet2 the formula would be:
      {=INDEX($C$2:$C$11,MATCH(E2&F2,Sheet2!$A$2:$A$11&Sheet2!$B$2:$B$11,0))}

  2. If you have low and high price.
    How to make lookup select it self price which is connected to one customer.
    If customer one have low price.

Leave a Reply

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

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 Youtube