Trying to pull up price for matching product?

[mrajotte]

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",A5897,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

 

[John C]

Try:
E19:
=IF(C19="","",VLOOKUP(C19,$A$58:$B$97,2,FALSE))

Hope this helps.

 

[T. Valko]

One way...

=SUMIF(A58:A97,C19,D5897)

 

[John C]

I think his VLOOKUP is wrong. His sample data shows his prices in column B,
not D.

 

[T. Valko]

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!

 

[John C]

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.

 

[mrajotte]

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]

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",A5897,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 (A5897), 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

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!!!

Try:
E19:
=IF(C19="","",VLOOKUP(C19,$A$58:$B$97,2,FALSE))

Hope this helps.