Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using V or H lookup
Hi,
I have a chart which is a price list. in one column I have the size of the product, next to it is a column with the product name, and next to it is a column with its price. I want to do a form which looks up a product and gives me the price. Only problem is that we have alot of the same product names and sizes. How can I have excel look it up. I thought maybe combining the size and product columns but that would mess up my Price list. Example: 12oz Whole Jal Price 26oz Whole Jal Price 12oz Sliced Jal Price 26oz Sliced Jal Price Can anyone help me? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using V or H lookup
You could combine the size and product in a helper column which is
some way to the right of your price list (eg column Z), so that it won't be seen. Then you could use an INDEX/MATCH combination to get the Price. But anyway, what is wrong with a price list like this: Whole Jal 12oz Price Whole Jal 26oz Price Sliced Jal 12oz Price Sliced Jal 26oz Price ? Hope this helps. Pete On Apr 22, 4:47*pm, katiapro93 wrote: Hi, I have a chart which is a price list. *in one column I have the size of the product, next to it is a column with the product name, and next to it is a column with its price. *I want to do a form which looks up a product and gives me the price. *Only problem is that we have alot of the same product names and sizes. *How can I have excel look it up. *I thought maybe combining the size and product columns but that would mess up my Price list. Example: 12oz * * * *Whole Jal * * *Price 26oz * * * *Whole Jal * * *Price 12oz * * * *Sliced Jal * * *Price 26oz * * * *Sliced Jal * * *Price Can anyone help me? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using V or H lookup
Thanks, this is getting me closer to what I want, I can combine the 2 columns
as a helper but then I have never used the Index/Match functions, can you go into it a little for me. I know I can look it up under help but I figured you would get me straight to the point. "Pete_UK" wrote: You could combine the size and product in a helper column which is some way to the right of your price list (eg column Z), so that it won't be seen. Then you could use an INDEX/MATCH combination to get the Price. But anyway, what is wrong with a price list like this: Whole Jal 12oz Price Whole Jal 26oz Price Sliced Jal 12oz Price Sliced Jal 26oz Price ? Hope this helps. Pete On Apr 22, 4:47 pm, katiapro93 wrote: Hi, I have a chart which is a price list. in one column I have the size of the product, next to it is a column with the product name, and next to it is a column with its price. I want to do a form which looks up a product and gives me the price. Only problem is that we have alot of the same product names and sizes. How can I have excel look it up. I thought maybe combining the size and product columns but that would mess up my Price list. Example: 12oz Whole Jal Price 26oz Whole Jal Price 12oz Sliced Jal Price 26oz Sliced Jal Price Can anyone help me? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using V or H lookup
Okay, I'll have to make some assumptions about where your data is
located. Assume that the three columns of your pricelist are on a sheet called Prices in columns A to C, and that in Z2 of that sheet you have this formula: =A2&B2 copied down. Then assume that you use E2 to enter the size and F2 to enter the product of the item you are interested in and that you want the price returned to G2. Put this formula in G2: =INDEX(Prices!C:C,MATCH(E2&F2,Prices!Z:Z,0)) E2, F2 and G2 could be in a different sheet. Hope this helps. Pete On Apr 22, 6:06*pm, katiapro93 wrote: Thanks, this is getting me closer to what I want, I can combine the 2 columns as a helper but then I have never used the Index/Match functions, can you go into it a little for me. *I know I can look it up under help but I figured you would get me straight to the point. "Pete_UK" wrote: You could combine the size and product in a helper column which is some way to the right of your price list (eg column Z), so that it won't be seen. Then you could use an INDEX/MATCH combination to get the Price. But anyway, what is wrong with a price list like this: Whole Jal 12oz * * * *Price Whole Jal 26oz * * * *Price Sliced Jal 12oz * * * *Price Sliced Jal 26oz * * * *Price ? Hope this helps. Pete On Apr 22, 4:47 pm, katiapro93 wrote: Hi, I have a chart which is a price list. *in one column I have the size of the product, next to it is a column with the product name, and next to it is a column with its price. *I want to do a form which looks up a product and gives me the price. *Only problem is that we have alot of the same product names and sizes. *How can I have excel look it up. *I thought maybe combining the size and product columns but that would mess up my Price list. Example: 12oz * * * *Whole Jal * * *Price 26oz * * * *Whole Jal * * *Price 12oz * * * *Sliced Jal * * *Price 26oz * * * *Sliced Jal * * *Price Can anyone help me?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |