IF Statement Combined with VLOOKUP

[yvettew78]

I have the following formula and I need to add three more if statements to the formula but I am not sure how to make it work together.

Current Formula:
=vlookup(B17,if(E4="Residential-House",ExtrasPricingList),2,0)

Other statements to add:
=vlookup(B17,if(E4="Residential-Shed",ExtrasPricingList),4,0)
=vlookup(B17,if(E4="Commercial",ExtrasPricingList),6,0)
=If(E4="Select Extras", THEN B17="Select Extras") - NOT SURE ABOUT THIS ONE

 

[RAJESH NATH]

I have the following formula and I need to add three more if statements to the formula but I am not sure how to make it work together.

Current Formula:
=vlookup(B17,if(E4="Residential-House",ExtrasPricingList),2,0)

Other statements to add:
=vlookup(B17,if(E4="Residential-Shed",ExtrasPricingList),4,0)
=vlookup(B17,if(E4="Commercial",ExtrasPricingList),6,0)
=If(E4="Select Extras", THEN B17="Select Extras") - NOT SURE ABOUT THIS ONE

kindly share you sheet

 

[yvettew78]

rA

kindly share you sheet

Hi Rajesh
I would rather not share it as it is a work form.
I just need to combine all those statements into one.

=vlookup(B17,if(E4="Residential-House",ExtrasPricingList),2,0),vlookup(B17,if(E4="Residential-Shed",ExtrasPricingList),4,0),vlookup(B17,if(E4="Commercial",ExtrasPricingList),6,0),If(E4="Select Extras", THEN B17="Select Extras")

The last function is if E4 reads "Select Extras", then i want to show B17 with "Select Extras" as well.

 

[Eric W]

For the 1st three, try:

=VLOOKUP(B17,ExtrasPricingList,IF(E4="Residential-House",2,IF(E4="Residential-Shed",4,IF(E4="Commercial",6))),0)

For the last one, you can't change the value of a different cell with a formula. A formula only affects the value of the cell that it is in. I don't know what you have in B17, but if it is "x", then you could put this in B17:

=IF(E4="Select Extras","Select Extras","x")

but I'm having a hard time understanding why. For the first formula, you'd end up looking up "Select Extras" in the ExtrasPricingList, and there's no column there to look it up.

 

[yvettew78]

For the 1st three, try:

=VLOOKUP(B17,ExtrasPricingList,IF(E4="Residential-House",2,IF(E4="Residential-Shed",4,IF(E4="Commercial",6))),0)

For the last one, you can't change the value of a different cell with a formula. A formula only affects the value of the cell that it is in. I don't know what you have in B17, but if it is "x", then you could put this in B17:

=IF(E4="Select Extras","Select Extras","x")

but I'm having a hard time understanding why. For the first formula, you'd end up looking up "Select Extras" in the ExtrasPricingList, and there's no column there to look it up.

Thanks for this, however I still come up with a #Value error.

Here is a screenshot of the sheet I am referring to where I want it to lookup values based on what is chosen at Cell E4.

 

[Eric W]

Given your layout, try:

Book1
	A	B	C	D	E
1
2
3
4					Residential - Shed
5
6
7
8
9
10
11
12
13
14
15
16
17		Tiled Roof Charge	251
Sheet3

Cell Formulas
Range	Formula
C17	C17	=VLOOKUP(B17,OFFSET(ExtrasPricingList,0,MATCH(E4,INDEX(ExtrasPricingList,1,0),0)-1,,2),2,0)

Given the long descriptions, I hope the values in E4 and B17 are generated from dropdowns, otherwise you'll have some spelling issues.