Lookup values to display

[ShiftyThor]

Hi Guys,

I am trying to wrap my head around the HLOOKUP function or which ever one is the best.

I need to have an answer cell lookup a value from a drop down menu as below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Price
[/TD]
[TD]Product
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PPN
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]HD
[/TD]
[/TR]
</tbody>[/TABLE]

That will be on one sheet. "Product" has a drop down menu for the different products.

The sheet with all he information is as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Price
[/TD]
[TD]Product
[/TD]
[/TR]
[TR]
[TD]1.90
[/TD]
[TD]PPN
[/TD]
[/TR]
[TR]
[TD]2.10
[/TD]
[TD]HD
[/TD]
[/TR]
</tbody>[/TABLE]

Basically the first sheet is like an order form, and the second sheet just has the prices. I would like to select the drop down menu on the first sheet (I have already made the drop down menu) and when a value is selected to check on the second sheet and pick the price which will show on sheet1 A1.

Many thanks

 

[Weazel]

assuming there wouldn't be any duplicates maybe...

D2=SUMIF(Sheet2!$B$2:$B$3,E2,Sheet2!$A$2)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Price[/TD]
[TD]Product[/TD]
[TD][/TD]
[TD]Price[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1.9​

[/TD]
[TD]PPN[/TD]
[TD][/TD]
[TD]

1.9​

[/TD]
[TD]PPN[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

2.1​

[/TD]
[TD]HD[/TD]
[TD][/TD]
[TD]

2.1​

[/TD]
[TD]HD[/TD]
[/TR]
</tbody>[/TABLE]

 

[ShiftyThor]

Basically sheet one will be as below with a number of rows:

Customer	Price	Product
Customer 1		PPN
Customer 2		PPN
Customer 3		HD
Customer 4		PPN

<tbody>
</tbody>

Sheet 2 also has a number of products

[TABLE="width: 500"]
<tbody>[TR]
[TD]Price
[/TD]
[TD]Product
[/TD]
[/TR]
[TR]
[TD]1.90
[/TD]
[TD]PPN
[/TD]
[/TR]
[TR]
[TD]2.10
[/TD]
[TD]HD
[/TD]
[/TR]
[TR]
[TD]3.00
[/TD]
[TD]LD
[/TD]
[/TR]
[TR]
[TD]1.80
[/TD]
[TD]HB
[/TD]
[/TR]
[TR]
[TD]1.85
[/TD]
[TD]PB
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

so basically what are you looking to do? the sumif is simply getting the price based on the item.

 

[ShiftyThor]

Sorry I have re-edited the above post as I was not so clear. I hope it is clearer now.
Thank you for the help so far.

 

[Weazel]

ok, still pretty much the same....

=SUMIF(Sheet2!$B$2:$B$6,F2,Sheet2!$A$2:$A$6)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Price[/TD]
[TD]Product[/TD]
[TD][/TD]
[TD]Customer[/TD]
[TD]Price[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1.9​

[/TD]
[TD]PPN[/TD]
[TD][/TD]
[TD]Customer 1[/TD]
[TD]

1.9​

[/TD]
[TD]PPN[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

2.1​

[/TD]
[TD]HD[/TD]
[TD][/TD]
[TD]Customer 2[/TD]
[TD]

1.9​

[/TD]
[TD]PPN[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]LD[/TD]
[TD][/TD]
[TD]Customer 3[/TD]
[TD]

2.1​

[/TD]
[TD]HD[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

1.8​

[/TD]
[TD]HB[/TD]
[TD][/TD]
[TD]Customer 4[/TD]
[TD]

1.9​

[/TD]
[TD]PPN[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

1.85​

[/TD]
[TD]PB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ShiftyThor]

Thank you so much. I now have it!!! You are a legend.