If statement, need solution

Tommy753

New Member
Joined
Jul 25, 2017
Messages
9
[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
Quartz Drop down select type
Quartz Price $$$$$

Granite Price $$$$

Solid Surface Price $$$$
Copy to PriceCell $$$

<tbody>
</tbody>
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi,

You mean something like this?


Book1
ABCDE
1MaterialPriceMaterialGranite
2Quartz100Price150
3Granite150
4Solid Surface75
Sheet169
Cell Formulas
RangeFormula
E2=VLOOKUP(E1,$A$2:$B$4,2,0)
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Can you upload a sample file to a free file host like Dropbox and post the link here.
 
Upvote 0
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
FGHIJKLMNOPQRTUVWXYZAA
38Quartz F207Granite H207Solid Surface L207Quartz F207
39$ 3,250.00$ 2,300.00$ 1,750.00Countertop Price10%$3,250.00
Price Sheet
Cell Formulas
RangeFormula
Y39=HLOOKUP(Y38,F38:L39,2,0)



Book1
AA
75Select
76Quartz F207
77Granite H207
78Solid 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).
 
Last edited:
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top