Way to use Vlookup function in Data Validation

 

Original Question:-

How to use the Vlookup function in data validation drop down list?

I’m working on a sheet that shows various discounts for products based on how much volume of product is purchased. I have a data validation box that lists the various amounts required (1 case, 2 case, etc.), and I have a sheet that has all the data (A1 is empty – column A is the case quantity – column B has a product name in B1 and then each discount based on the case quantity in Column A). I want the discount amount in Sheet 2 to fill in automatically based on the information on Sheet 1.

 

image 1

 

To meet this requirement we will create the drop down list to product and product code and then we use “Index” and “Match” function to return the amount according to product and product code.

Create the drop down list for product:-

  • Select the cell C15.
  • Go to Data tab > Data Validation > Setting > List > Source (B4:L4).

image 2

 

  • Select the cell D15.
  • Go to Data tab > Data Validation > Setting > List > Source (A5:A10).

image 3

 

  • Now Enter the formula in Cell E15.
  • =INDEX($A$4:$L$10,MATCH(D$15,$A$4:$A$10,0),MATCH(C15,$A$4:$L$4))
  • Press Enter.

image 4

 

Whenever you will change the product and product code from the drop down list amount will get changed automatically.



Example:


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>