Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to pull up price for matching product?
Hello ... I'm sure this is a very simple issue, but I just cant figure it out!
I am creating a purcahse order and have a list of product name. I want to cell next to it to pull the associated price for that product name. My data is listed as: A B 58 name1 price1 59 name2 price2 60 name3 price3 61 name 4 price4 .... 97 name40 price40 In cell C19 I have a list of all product names (A58 - A97), and in cell E19 I have the following formula: =VLOOKUP("C19",A58:D97,4,FALSE) I'm trying to have the formula be flexible and use ANY value it sees in C19 as the value to look up in the cells below, rather than telling it a specific value to search for. Is this possible? How can i get it to simply pull the associated price for the product selected in the pull down list? Thank you for any help!! Michelle |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to pull up price for matching product?
Try:
E19: =IF(C19="","",VLOOKUP(C19,$A$58:$B$97,2,FALSE)) Hope this helps. -- John C "mrajotte" wrote: Hello ... I'm sure this is a very simple issue, but I just cant figure it out! I am creating a purcahse order and have a list of product name. I want to cell next to it to pull the associated price for that product name. My data is listed as: A B 58 name1 price1 59 name2 price2 60 name3 price3 61 name 4 price4 ... 97 name40 price40 In cell C19 I have a list of all product names (A58 - A97), and in cell E19 I have the following formula: =VLOOKUP("C19",A58:D97,4,FALSE) I'm trying to have the formula be flexible and use ANY value it sees in C19 as the value to look up in the cells below, rather than telling it a specific value to search for. Is this possible? How can i get it to simply pull the associated price for the product selected in the pull down list? Thank you for any help!! Michelle |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to pull up price for matching product?
One way...
=SUMIF(A58:A97,C19,D58:D97) -- Biff Microsoft Excel MVP "mrajotte" wrote in message ... Hello ... I'm sure this is a very simple issue, but I just cant figure it out! I am creating a purcahse order and have a list of product name. I want to cell next to it to pull the associated price for that product name. My data is listed as: A B 58 name1 price1 59 name2 price2 60 name3 price3 61 name 4 price4 ... 97 name40 price40 In cell C19 I have a list of all product names (A58 - A97), and in cell E19 I have the following formula: =VLOOKUP("C19",A58:D97,4,FALSE) I'm trying to have the formula be flexible and use ANY value it sees in C19 as the value to look up in the cells below, rather than telling it a specific value to search for. Is this possible? How can i get it to simply pull the associated price for the product selected in the pull down list? Thank you for any help!! Michelle |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to pull up price for matching product?
I think his VLOOKUP is wrong. His sample data shows his prices in column B,
not D. :-P -- John C "T. Valko" wrote: One way... =SUMIF(A58:A97,C19,D58:D97) -- Biff Microsoft Excel MVP "mrajotte" wrote in message ... Hello ... I'm sure this is a very simple issue, but I just cant figure it out! I am creating a purcahse order and have a list of product name. I want to cell next to it to pull the associated price for that product name. My data is listed as: A B 58 name1 price1 59 name2 price2 60 name3 price3 61 name 4 price4 ... 97 name40 price40 In cell C19 I have a list of all product names (A58 - A97), and in cell E19 I have the following formula: =VLOOKUP("C19",A58:D97,4,FALSE) I'm trying to have the formula be flexible and use ANY value it sees in C19 as the value to look up in the cells below, rather than telling it a specific value to search for. Is this possible? How can i get it to simply pull the associated price for the product selected in the pull down list? Thank you for any help!! Michelle |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to pull up price for matching product?
Yeah, but the formula they posted shows column D so that's what I went with.
I have a 50/50 chance of getting it right! -- Biff Microsoft Excel MVP "John C" <johnc@stateofdenial wrote in message ... I think his VLOOKUP is wrong. His sample data shows his prices in column B, not D. :-P -- John C "T. Valko" wrote: One way... =SUMIF(A58:A97,C19,D58:D97) -- Biff Microsoft Excel MVP "mrajotte" wrote in message ... Hello ... I'm sure this is a very simple issue, but I just cant figure it out! I am creating a purcahse order and have a list of product name. I want to cell next to it to pull the associated price for that product name. My data is listed as: A B 58 name1 price1 59 name2 price2 60 name3 price3 61 name 4 price4 ... 97 name40 price40 In cell C19 I have a list of all product names (A58 - A97), and in cell E19 I have the following formula: =VLOOKUP("C19",A58:D97,4,FALSE) I'm trying to have the formula be flexible and use ANY value it sees in C19 as the value to look up in the cells below, rather than telling it a specific value to search for. Is this possible? How can i get it to simply pull the associated price for the product selected in the pull down list? Thank you for any help!! Michelle |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to pull up price for matching product?
50/50? Nah, not that high (nor me for that matter), back in the old days, we
had what we called the 50/50/90 rule, where if you have a 50% chance of getting something right, then you would get it wrong 90% of the time. :) -- John C "T. Valko" wrote: Yeah, but the formula they posted shows column D so that's what I went with. I have a 50/50 chance of getting it right! -- Biff Microsoft Excel MVP "John C" <johnc@stateofdenial wrote in message ... I think his VLOOKUP is wrong. His sample data shows his prices in column B, not D. :-P -- John C "T. Valko" wrote: One way... =SUMIF(A58:A97,C19,D58:D97) -- Biff Microsoft Excel MVP "mrajotte" wrote in message ... Hello ... I'm sure this is a very simple issue, but I just cant figure it out! I am creating a purcahse order and have a list of product name. I want to cell next to it to pull the associated price for that product name. My data is listed as: A B 58 name1 price1 59 name2 price2 60 name3 price3 61 name 4 price4 ... 97 name40 price40 In cell C19 I have a list of all product names (A58 - A97), and in cell E19 I have the following formula: =VLOOKUP("C19",A58:D97,4,FALSE) I'm trying to have the formula be flexible and use ANY value it sees in C19 as the value to look up in the cells below, rather than telling it a specific value to search for. Is this possible? How can i get it to simply pull the associated price for the product selected in the pull down list? Thank you for any help!! Michelle |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to pull up price for matching product?
Thank you!!! Worked like charm ...
So the issue was that I needed to add an IF function that said no matter what C19 was [","], go to the look up function? Thank you!!! "John C" wrote: Try: E19: =IF(C19="","",VLOOKUP(C19,$A$58:$B$97,2,FALSE)) Hope this helps. -- John C "mrajotte" wrote: Hello ... I'm sure this is a very simple issue, but I just cant figure it out! I am creating a purcahse order and have a list of product name. I want to cell next to it to pull the associated price for that product name. My data is listed as: A B 58 name1 price1 59 name2 price2 60 name3 price3 61 name 4 price4 ... 97 name40 price40 In cell C19 I have a list of all product names (A58 - A97), and in cell E19 I have the following formula: =VLOOKUP("C19",A58:D97,4,FALSE) I'm trying to have the formula be flexible and use ANY value it sees in C19 as the value to look up in the cells below, rather than telling it a specific value to search for. Is this possible? How can i get it to simply pull the associated price for the product selected in the pull down list? Thank you for any help!! Michelle |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to pull up price for matching product?
If you see my notes in the other part of this thread, I talk a little bit
about it. The C19 is fine, I just added in a little bit of error checking in it, and would have added a little more, but you stated that C19 is a list of the choices. Currently, if C19 was blank, it might have given you an error without the IF portion. Now, on to what your formula looked like vice mine in the matter of the actual lookup. VLOOKUP(C19,$A$58:$B$97,2,FALSE) .... mine VLOOKUP("C19",A58:D97,4,FALSE) .... yours First mine, my statement takes a look at whatever is in C19, finds it's match in column A, rows 58 through 97, then finds the value in column 2 of my table $A$58:$A$97, that is an exact match (as determined by FALSE). Your formula, first you list C19 in quotations, which means your lookup is looking to find C19 (the letter and 2 numbers, not the value/text in the cell C19) in column A, rows 48 through 97. Then, you have your table listed as 4 column wide (A58:D97), and want to match C19 from column A, and return the exact match (as determined by FALSE), from column 4. Hope this helps explain a little, and thanks for the feedback. -- John C "mrajotte" wrote: Thank you!!! Worked like charm ... So the issue was that I needed to add an IF function that said no matter what C19 was [","], go to the look up function? Thank you!!! "John C" wrote: Try: E19: =IF(C19="","",VLOOKUP(C19,$A$58:$B$97,2,FALSE)) Hope this helps. -- John C "mrajotte" wrote: Hello ... I'm sure this is a very simple issue, but I just cant figure it out! I am creating a purcahse order and have a list of product name. I want to cell next to it to pull the associated price for that product name. My data is listed as: A B 58 name1 price1 59 name2 price2 60 name3 price3 61 name 4 price4 ... 97 name40 price40 In cell C19 I have a list of all product names (A58 - A97), and in cell E19 I have the following formula: =VLOOKUP("C19",A58:D97,4,FALSE) I'm trying to have the formula be flexible and use ANY value it sees in C19 as the value to look up in the cells below, rather than telling it a specific value to search for. Is this possible? How can i get it to simply pull the associated price for the product selected in the pull down list? Thank you for any help!! Michelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Product & Price lists | Excel Worksheet Functions | |||
how to set up quotes that pull from price book | Excel Discussion (Misc queries) | |||
I have three tables; each table has a product and a price next to | Excel Discussion (Misc queries) | |||
Get product only when you have price and amount in stock | New Users to Excel | |||
at, if statement with text, such as if(product), then (price) | Excel Worksheet Functions |