Retrieving the Price for a Specified Item and a Specified Brand in Microsoft Excel 2010

To retrieve the price for a specified item that matches with specified brand, we will use a combination of "IF" & "LOOKUP" functions to get the output.
The "IF function" checks if a condition you specify is TRUE or FALSE. If the condition returns TRUE then it returns preset value, and if the condition returns FALSE then it returns another preset value.

Syntax = IF(logical_test,value_if_true,value_if_false)
"LOOKUP":Looks up a value either from a one-row or one-column range or from an array. Provided for backward compatibility.

Syntax:=LOOKUP(lookup_value,lookup_vector,result_vector)=LOOKUP(lookup_value,array)
Let us take an example:

We have list of items in column A and Brand list in column B. We need to find a formula that will give the price as a result in column C based on the conditions matched in column A & B. The fixed price list is saved in column E to G.

img1

  • In cell C2, the formula would be
  • =IF(A2="Jeans",LOOKUP(B2,{"Diesel","Lee","Levis";85,70,80}),IF(A2="T-Shirt",LOOKUP(B2,{"Gap","Old Navy","Sacks"},{5,3,7})))
  • Press enter on your keyboard.
  • The function will return the retrieving price for a specified item and a specified brand.

img2

  • Copying the formula down from cell C2 to range C3:C7.

img3

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