Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup or similar
I have some trees and i want to get some prices for them in different years.
How can i do it? For example: Orange trees (OT) and bananas (BA) that are 1,2,3,4,5 years old. The prices for every type of tree is different. OT-1year - $100, OT-2Years - $200 and BA-1year - $300, BA-2years - $400.etc. How can i make a commant that will recognize the type of tree, its age and give me the price that i want. Please i really need it. |
#2
|
|||
|
|||
Create a table in say M1:R100 to look like
Tree Year1 Year2 Year3 Year4 Year5 Orange 100 200 300 400 500 Banana 300 400 450 500 700 or whatvere values, and then just use =VLOOKUP("Orange",M1:R100, year+1,False) year+1 is 2 for one year old tress etc. -- HTH Bob Phillips "Andycleos" wrote in message ... I have some trees and i want to get some prices for them in different years. How can i do it? For example: Orange trees (OT) and bananas (BA) that are 1,2,3,4,5 years old. The prices for every type of tree is different. OT-1year - $100, OT-2Years - $200 and BA-1year - $300, BA-2years - $400.etc. How can i make a commant that will recognize the type of tree, its age and give me the price that i want. Please i really need it. |
#3
|
|||
|
|||
Thanks for the reply but i have one more question,
I need a cell that will combine the two functions. That means that i need a cell that will give me lets say number 200 everytime that i have in another cell "Orange2". My problem is that i have too much data for one formula of lookup. "Bob Phillips" wrote: Create a table in say M1:R100 to look like Tree Year1 Year2 Year3 Year4 Year5 Orange 100 200 300 400 500 Banana 300 400 450 500 700 or whatvere values, and then just use =VLOOKUP("Orange",M1:R100, year+1,False) year+1 is 2 for one year old tress etc. -- HTH Bob Phillips "Andycleos" wrote in message ... I have some trees and i want to get some prices for them in different years. How can i do it? For example: Orange trees (OT) and bananas (BA) that are 1,2,3,4,5 years old. The prices for every type of tree is different. OT-1year - $100, OT-2Years - $200 and BA-1year - $300, BA-2years - $400.etc. How can i make a commant that will recognize the type of tree, its age and give me the price that i want. Please i really need it. |
#4
|
|||
|
|||
=VLOOKUPA2,M1:R100, year+1,False)
-- HTH Bob Phillips "andycleos" wrote in message ... Thanks for the reply but i have one more question, I need a cell that will combine the two functions. That means that i need a cell that will give me lets say number 200 everytime that i have in another cell "Orange2". My problem is that i have too much data for one formula of lookup. "Bob Phillips" wrote: Create a table in say M1:R100 to look like Tree Year1 Year2 Year3 Year4 Year5 Orange 100 200 300 400 500 Banana 300 400 450 500 700 or whatvere values, and then just use =VLOOKUP("Orange",M1:R100, year+1,False) year+1 is 2 for one year old tress etc. -- HTH Bob Phillips "Andycleos" wrote in message ... I have some trees and i want to get some prices for them in different years. How can i do it? For example: Orange trees (OT) and bananas (BA) that are 1,2,3,4,5 years old. The prices for every type of tree is different. OT-1year - $100, OT-2Years - $200 and BA-1year - $300, BA-2years - $400.etc. How can i make a commant that will recognize the type of tree, its age and give me the price that i want. Please i really need it. |
#5
|
|||
|
|||
So what do i have to write in cell (XXXXX) under VALUE in order for it to
match whatever is writen under the cell TREE? Everytime cell TREE will change. In this example the value will be 400. I forgot to mention that Orange4 will come out of another formula. Year1 Year2 Year3 Year4 Year5 Orange 100 200 300 400 500 Banana 300 400 500 600 700 Apple 500 600 700 800 900 TREE VALUE Orange4 XXXXXXX |
#6
|
|||
|
|||
You've lost me.
-- HTH Bob Phillips "andycleos" wrote in message ... So what do i have to write in cell (XXXXX) under VALUE in order for it to match whatever is writen under the cell TREE? Everytime cell TREE will change. In this example the value will be 400. I forgot to mention that Orange4 will come out of another formula. Year1 Year2 Year3 Year4 Year5 Orange 100 200 300 400 500 Banana 300 400 500 600 700 Apple 500 600 700 800 900 TREE VALUE Orange4 XXXXXXX |
#7
|
|||
|
|||
Sorry !!!!!!
I have a formula that gives me the tree's age. thus i get the Orange1,2,3,4 under the cell called TREE . What i want, is a formula that will take the combined information from this cell (TREE) and also the table (that i show you in the prviews letter) and give me the value of the specific tree at a specific age. hope that you understant me this time Thank you very much for the help |
#8
|
|||
|
|||
That's okay, we all know what WE mean <vbg
I think this maybe what you want =VLOOKUP(LEFT(Tree,LEN(Tree)-1),M1:R100, RIGHT(Tree,1)+1,FALSE) -- HTH Bob Phillips "andycleos" wrote in message ... Sorry !!!!!! I have a formula that gives me the tree's age. thus i get the Orange1,2,3,4 under the cell called TREE . What i want, is a formula that will take the combined information from this cell (TREE) and also the table (that i show you in the prviews letter) and give me the value of the specific tree at a specific age. hope that you understant me this time Thank you very much for the help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |