If statement, need solution

[Tommy753]

[Use drop down selecting Quartz, Granite or solid surface (Drop down created) Cause price on left to populate price cell. Help would be appreciate finding a formula
[TABLE="width: 1241"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD="colspan: 4"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Quartz Drop down select type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD="colspan: 3"]Quartz Price $$$$$

Granite Price $$$$

Solid Surface Price $$$$[/TD]
[TD][/TD]
[TD="colspan: 6"][/TD]
[TD][/TD]
[TD]Copy to PriceCell $$$[/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 17"][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

You mean something like this?

Book1
	A	B	C	D	E
1	Material	Price		Material	Granite
2	Quartz	100		Price	150
3	Granite	150
4	Solid Surface	75
Sheet169

Cell Formulas
Range	Formula
E2	=VLOOKUP(E1,$A$2:$B$4,2,0)

 

[Tommy753]

Thank you, With E1 being a drop down to select. When I get in front of my computer I will apply and let you know...

 

[Tommy753]

I was thinking more an If statement because, from your example:

Lets make E1 a Data Validation drop-down with Select, Quartz, Granite, Solid Surface

When Quartz is current in E1, then populate the value in B2 or $100 into E2
When Granite is Current in E1, then populate the value in B3 or $150 into E2
and so on with Solid surface...

Only need cells E1(drop-down), B2,3,4 and result in E2 Where value in E1 causes data in either B2,B3 or B4 to be moved to E2.

Note: Price for each of the surfaces are being calculate from a workbook in another tab and that value is being referred to the B1-3 cells.

I hope that makes my goal a little clearer Thanks, Tommy

 

[jtakw]

For what you're describing, VLOOKUP is the proper function to use rather than a Nested IF.

Are you saying the formula provided is Not working?
If the formula is Not giving you the correct results, it would most likely mean the Values/Texts in your drop down does Not match the Values/Texts in the Table (Column A), check for leading and or trailing Space(s) in both.

 

[Tommy753]

I looked closely at the formula and I even created a new scenario using simple references and it just continue to return na I'm certain my references were correct

 

[jtakw]

Can you upload a sample file to a free file host like Dropbox and post the link here.

 

[Tommy753]

Thanks for staying with me on this.....Tommy

https://drive.google.com/open?id=1IEdYqcF5fvt3eNTHIJUv6Bt07naUFsoK

 

[jtakw]

Ok,

There a few problems here:

1. Your data is Horizontal, Not Vertical as in your OP and description.
2. Your Data Validation list Does Not match the Table F38, H38, and L38.
3. You have a lot of merged cells, which could cause problems, but for this matter, it's Ok.

Solution:

Change the DV list to Match F38, H38, and L38
Use HLOOKUP instead of VLOOKUP

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	T	U	V	W	X	Y	Z	AA
38	Quartz F207		Granite H207				Solid Surface L207												Quartz F207
39	$ 3,250.00		$ 2,300.00				$ 1,750.00				Countertop Price							10%	$3,250.00
Price Sheet

Cell Formulas
Range	Formula
Y39	=HLOOKUP(Y38,F38:L39,2,0)

Book1
	AA
75	Select
76	Quartz F207
77	Granite H207
78	Solid Surface L207
Price Sheet

PS: you should have also clarified which cell the formula was supposed to be in, I had to look thru a bunch of cells to figure it out (cause it's not E1).

 

[Tommy753]

Thank you for your help! I was finally able to get it to work.

When selecting the data field I was incorrectly selecting the header field not both the header and the data fields together.

Once I figured that out, worked like a charm...