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.
 
 

Comments

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

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

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.