Multiple Drop down to select displayed price

[lonnie451]

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]

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

=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
	B	C	D	E	F
9	Product	Qty	Description	Price	Total
10	Grapes	1LB		#N/A
11
12
13
14
15
16
Sheet1

Cell Formulas
Range	Formula
E10	E10	=VLOOKUP(B10,Sheet2!$A$4:$D$7,MATCH(C10,Sheet2!$A$4:$D$4,0),0)

Cells with Data Validation
Cell	Allow	Criteria
B10	List	=Sheet2!$A$4:$A$6
C10	List	=Sheet2!$B$3:$D$3

 

[lonnie451]

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

 

[Marcílio_Lobão]

*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]

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]

Looking at my test book again I now realise I'd not mimicked yours!!
Just indulge me and try this.

=VLOOKUP(B10,Sheet2!$A$4:$D$6,MATCH(C10,Sheet2!$A$3:$D$3,0),0)

 

[lonnie451]

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
	B	C	D	E	F	G
9	Product	Qty		Description	Price	Total
10	Bananas	3LB			2.45
11					#N/A
12					#N/A
13					#N/A
14					#N/A
15					#N/A
16					#N/A
Sheet1

Cell Formulas
Range	Formula
F10:F16	F10	=VLOOKUP(B10,Sheet2!$A$4:$D$6,MATCH(Sheet1!C10,Sheet2!$B$3:$D$3,0)+1,FALSE)

Cells with Data Validation
Cell	Allow	Criteria
B10:B16	List	=Sheet2!$A$4:$A$6
C10:D16	List	=Sheet2!$B$3:$D$3

 

[Marcílio_Lobão]

*Ionnie451, Good afternoon.

To solve this question use a IFERROR function.

=IFERROR( your formula, "")

F10 -->

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