# Multiple Drop down to select displayed price

#### lonnie451

##### New Member
Hello...

Have a small issue...

I have 2 drop down menus.. one for the product name and the other for the product size.. I am wanting to select the price for the size and brand of a particular product.
I guess what I am looking for is " I select bellow in drop down one and Medium in Drop down 2, the price will be reflected in the proper cell."

All help is appreciated..

#### lonnie451

##### New Member
*lonnie451.

From all the info and the exact LAYOUT that you now provided; this will work to give a “Price” in E10, dependant on the selections made in B10 and C10.

Excel Formula:
``=VLOOKUP(B10,Sheet2!\$A\$4:\$D\$7,MATCH(C10,Sheet2!\$A\$4:\$D\$4,0),0)``

If the you change the layout of either Sheet 1 or Sheet2, then cell references will have to be changed accordingly to reflect the changes
didn't work and I haven't changed anything...
Book3
BCDEF
9ProductQtyDescriptionPriceTotal
10Grapes1LB#N/A
11
12
13
14
15
16
Sheet1
Cell Formulas
RangeFormula
E10E10=VLOOKUP(B10,Sheet2!\$A\$4:\$D\$7,MATCH(C10,Sheet2!\$A\$4:\$D\$4,0),0)
Cells with Data Validation
CellAllowCriteria
B10List=Sheet2!\$A\$4:\$A\$6
C10List=Sheet2!\$B\$3:\$D\$3

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### lonnie451

##### New Member
this seems to work..
=VLOOKUP(B10,Sheet2!\$A\$4:\$D\$6,MATCH(Sheet1!C10,Sheet2!B3:D3,0)+1,FALSE)

#### Marcílio_Lobão

##### Well-known Member
*Ionnie451, Bom dia.

As fórmulas que o colega julhs e eu lhe fornecemos (que é praticamente a mesma) funcionam corretamente dentro do layout que você nos forneceu nos modelos.

A questão que você está mencionando na função Match é porque você está fazendo a abordagem do intervalo de maneira equivocada.

Você deve sempre considerar toda a linha do cabeçalho. A3:D3

Assim quando você selecionar a primeira opção 1LB , o Match trará a resposta de 2, que é exatamente a coluna da tabela de preços que você precisa para usar o Vlookup.

Se você começa com B3:D3 a primeira opção 1LB trará a resposta 1, fazendo o Vlookup procurar na coluna 1 que é o nome dos produtos.

Esse tipo de pesquisa é clássico e não precisa inventar nada novo.
É sempre a mesma coisa em todos os tipos de pesquisas.

#### Marcílio_Lobão

##### Well-known Member
I'm sorry. I wrote all the message in Portuguese.
Today is a very hard day of work. Sorry!

* Ionnie451, Good morning.

The formulas that my colleague juhls and I provide him (which is practically the same) work correctly within the layout you provided us in the templates.

The question you are mentioning in the Match function is why you are misinterpreting the range.

You should always consider the entire header line. A3: D3

So when you select the first 1LB option, Match will give you the answer of 2, which is exactly the price table column you need to use Vlookup.

If you start with B3: D3 the first option 1LB will bring answer 1, making Vlookup look in column 1 which is the name of the products.

This type of research is classic and does not need to invent anything new.
It is always the same in all types of research.

Hope this helps.

#### julhs

##### Board Regular

Looking at my test book again I now realise I'd not mimicked yours!!
Just indulge me and try this.
Excel Formula:
``=VLOOKUP(B10,Sheet2!\$A\$4:\$D\$6,MATCH(C10,Sheet2!\$A\$3:\$D\$3,0),0)``

#### lonnie451

##### New Member
one more step...lol.. The #N/A.. when nothing is selected I want it blank, until something is selected in both Product and QTY..

Book3
BCDEFG
9ProductQtyDescriptionPriceTotal
10Bananas3LB2.45
11#N/A
12#N/A
13#N/A
14#N/A
15#N/A
16#N/A
Sheet1
Cell Formulas
RangeFormula
F10:F16F10=VLOOKUP(B10,Sheet2!\$A\$4:\$D\$6,MATCH(Sheet1!C10,Sheet2!\$B\$3:\$D\$3,0)+1,FALSE)
Cells with Data Validation
CellAllowCriteria
B10:B16List=Sheet2!\$A\$4:\$A\$6
C10:D16List=Sheet2!\$B\$3:\$D\$3

#### Marcílio_Lobão

##### Well-known Member
*Ionnie451, Good afternoon.

To solve this question use a IFERROR function.

F10
-->
Excel Formula:
``=IFERROR(VLOOKUP(B10,Sheet2!\$A\$4:\$D\$6,MATCH(C10,Sheet2!\$A\$3:\$D\$3,0),FALSE),"")``

It worked for you.
I hope it helps.

Replies
6
Views
190
Replies
5
Views
2K
Replies
9
Views
132
Replies
0
Views
84
Replies
1
Views
220

1,147,822
Messages
5,743,400
Members
423,792
Latest member
travisds

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back