Multiple Drop down to select displayed price

lonnie451

New Member
Joined
Mar 1, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 1, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
*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
Joined
Mar 1, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 7, 2013
Messages
789
Office Version
  1. 2007
Platform
  1. Windows
*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.

Espero ter ajudado.
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
789
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Dec 3, 2018
Messages
82
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 1, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 7, 2013
Messages
789
Office Version
  1. 2007
Platform
  1. Windows
*Ionnie451, Good afternoon.

To solve this question use a IFERROR function.

=IFERROR( your formula, "")

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.
 
Solution

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top