-1

I have a problem with excel! I need to check a list of values and if my value is exist between start and end in my list , get a price of which one is match in list

My product list this :

Id Data Product_Name Price
1 9905 Mouse $200
2 9915 Power $300
1 9925 Case $400

My Price list is :

Id Start End Price
1 9900 9910 $200
2 9911 9920 $300
3 9921 9930 $400

Check Data in Product list is between Start and End in Price list and if Data is between Start and End so get me a value of Price

thhank!

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Mohammad Esmaeili
  • 155
  • 2
  • 2
  • 12

1 Answers1

2

VLOOKUP will do this:

=VLOOKUP(B2,H:J,3)

One note, make sure the lookup table is sorted ascending on the start column.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Sorry i have a question again ! can check both Data and Product_Name with VLOOKUP ? – Mohammad Esmaeili Dec 18 '20 at 20:22
  • that is a new question, and you would want to switch to SUMIFS() for that. See: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Scott Craner Dec 18 '20 at 20:24
  • No this is not a new question , In my other price list i have Product_Name and i want to check if Date is between Start and End and Product_Name is equle my Product_Name then get me Price , Can do it with VLOOKUP ? – Mohammad Esmaeili Dec 18 '20 at 20:33
  • It is a new question because you are adding conditions to the original. This question only had to do with between start and end, no product name. Now you are adding product name, that makes it a new question. also the answer to your question can be found in the link I provided. – Scott Craner Dec 18 '20 at 20:36
  • Ok i add new question and please answer this :) – Mohammad Esmaeili Dec 18 '20 at 20:38
  • Please answer this , thank: https://stackoverflow.com/questions/65363654/vlookup-two-columns-in-excel – Mohammad Esmaeili Dec 18 '20 at 20:53