Multiple If Statements with VLookup

[yvettew78]

Hi Everyone

I am trying to enter a formula like this:-

If E3=Cash, E4=Residential & E5=Single, then I want it to lookup the quantity in Cell C10, then go to the datasheet called 'RRP - RESIDENTIAL Cash Price (Single Phase)' and choose the amount relating in this sheet - VLOOKUP(C10,'RRP - RESIDENTIAL Cash Price (Single Phase)!'A2:B58,2,FALSE))

If someone can help me that would be awesome.

Thank you.

 

[DanteAmor]

Hi and welcome to MrExcel

Try this:

=IF(AND(E3="Cash",E4="Residencial",E5="Single"),VLOOKUP(C10,'RRP - RESIDENTIAL Cash Price'!A2:B18,2,0))

 

[yvettew78]

Hi and welcome to MrExcel

Try this:

=IF(AND(E3="Cash",E4="Residencial",E5="Single"),VLOOKUP(C10,'RRP - RESIDENTIAL Cash Price'!A2:B18,2,0))

Thank you Dante
the following formula has worked
=IF(AND(E3="Cash",E4="Residential",E5="Single"),VLOOKUP(C10,'RRP - RESIDENTIAL Cash Price (Single Phase)'!A2:B58,2,FALSE))

now I have that working I also need to include the following formulas to existing formula as above.

if E3="Cash", E4="Residential", E5="Three",VLOOKUP(C10,'RRP - RESIDENTIAL Cash Price (Three Phase)'!A2:B58,2,FALSE))
if E3="Finance", E4="Residential", E5="Single",VLOOKUP(C10,'RRP - RESIDENTIAL Finance Price (Single Phase)'!A2:B58,2,FALSE))
if E3="Finance", E4="Residential", E5="Three",VLOOKUP(C10,' RRP - RESIDENTIAL Finance Price (Three Phase)'!A2:B58,2,FALSE))
if E3="Cash", E4="Commercial", E5="Three",VLOOKUP(C10,' RRP - COMMERCIAL Cash Price (Three Phase)'!A2:B58,2,FALSE))
if E3="Finance", E4="Commercial", E5="Three",VLOOKUP(C10,' RRP - COMMERCIAL Finance Price (Three Phase) '!A2:B58,2,FALSE))

If you could also help me here, that would be awesome.

Thanks!

 

[DanteAmor]

Try this:

=IF(OR(AND(E3="Cash",E4="Residential",E5="Single"),
AND(E3="Cash",E4="Residential",E5="Three"),
AND(E3="Finance",E4="Residential",E5="Single"),
AND(E3="Finance",E4="Residential",E5="Three"),
AND(E3="Cash",E4="Commercial",E5="Three"),
AND(E3="Finance",E4="Commercial",E5="Three")),
IFERROR(VLOOKUP(C10,'RRP - RESIDENTIAL Cash Price'!A2:B18,2,0),"Not Found"),"No match")

 

[jasonb75]

@DanteAmor you can make your logical test a bit shorter, either of these should work just as well, the first is closer to your original.

OR(AND(OR(E3={"Cash","Finance"}),E4="Residential",OR(E5={"Single","Three"})),AND(OR(E3={"Cash","Finance"}),E4="Commercial",E5="Three"))

AND(OR(E3={"Cash","Finance"}),IF(E4="Residential",OR(E5={"Single","Three"}),IF(E4="Commercial",E5="Three")))

I've just looked at post 3 and noticed a discrepancy in your formula and what was asked for, new formula below, but it uses volatile functions, not the best but it's a lot more compact than the alternative.

=IF(AND(OR(E3={"Cash","Finance"}),IF(E4="Residential",OR(E5={"Single","Three"}),IF(E4="Commercial",E5="Three"))),
IFERROR(VLOOKUP(C10,INDIRECT("'RRP - "&E4&" "&E3&" Price ("&E5&" Phase) '!A2:B58"),2,0),"Not Found"),"No match")

Without indirect, it's back to @DanteAmor's formula with the logical test split down and 6 different VLOOKUPS

 

[DanteAmor]

I've just looked at post 3 and noticed a discrepancy in your formula and what was asked for, new formula below, but it uses volatile functions, not the best but it's a lot more compact than the alternative.

You're right, the search is on 6 different sheets. My mistake, I didn't check that, I thought it was the same sheet. Thanks for comment.
Your formula is fantastic.

 

[yvettew78]

Thanks everyone