Vlookup Formula [SOLVED]

[Alanna 10]

Can anyone help me?


I need a formula for:


Flower Type/Length/Price

(next line-answer)

Aster/60cm/......?


Price List:

Aster/60cm/$1.80

Aster/70cm/$2.00


How can I get a formula to find out the price when I enter the flower type and the lengh (there may be a few different lengths for the same flower type.


I was using a formula: =VLOOKUP(D1317,$I$2:$J$121,2,FALSE) how ever this only works for flower type only not lenths as well.


Does this make sense?


Alanna

 

[Harry Covair]

Hi Alanna,

for a table like this

Aster 60 1,8

Aster 70 2

tulip 60 1,5

tulip 70 1,75


I suggest the sumproduct function:

=SUMPRODUCT((A1:A4 = "tulip")*(B1:B4=60)*(C1:C4)) as array formula thus confirm with ctrl-shift-enter


Regards


Harry

 

[NARAYANK991]

Hi Alanna ,


If you say the formula which you have posted was working for flower types , can I assume that your data is having flower types in column I , and the price in column J ?


If not , how is your data laid out ?


Secondly , how are you now going to specify the flower type and length ? Will both these items of data be entered in one cell e.g. Aster/60cm , or will Aster be entered in one cell , and 60cm be entered in another cell ?


Basically what you need to use is an INDEX + MATCH , where the MATCH will use a concatenation of the flower type and length ; this will work provided your data is laid out so that the flower types are in one column , the corresponding lengths in a second column , and the respective prices in the third column e.g.

[pre]

Aster     60     $1.80
Aster     70     $2.00

[/pre]
Narayan

 

[dibyendu.sharma]

If you are using Office 2007/2010 you can use SUMIFS() function. Or the easy solution is to add a new column in the front say Column A and then your from data from Column B for Flower/Size/Price. The column A will have the unique value based on the " & " operator. Basically =B2&C2...this will give you a unique Vlookup item. I am attaching a link to a file with both the example, hope that will help.


https://skydrive.live.com/embed?cid=560166A649EA2EC9&resid=560166A649EA2EC9%21753&authkey=AIJaydJZ7BruO58


Cheers!!

Dibs

 

[Alanna 10]

Thanks Harry, I will try your formula.


Narayan - the sencond option you were talking about having the flower type in one cell and the cms in another, exaclty how you have typed it in. I will have a price list with the variety type then the cms and then the price that relates to the cms. Then in my spreadsheet I will type in Aster and in another call I will type 60cm and then hopefully the price of 1.80 will appear in another cell.

Does this make sense.


Dibs - I will have a look at your option

 

[Alanna 10]

Dibs that example is exactly how I want to use it, however I cant see your formula in the example you gave me, can you give me a formula to use?


Alanna

 

[Debraj]

Hi Alanna!


In Skydrive.. File > Save As > Download..


It will gives you the original file posted by Dibyendu.. with Formula


Check and let us know..


Regards,

Deb

 

[sgmpatnaik]

@Alanna


Hi


Please Download the Dibyendu uploaded file which you are try to download


https://dl.dropboxusercontent.com/u/75654703/answersheet.xls


Thanks


SP

 

[Alanna 10]

Thank you, thank you,im very pleased it worked!!!