Multiple If Statements with VLookup

yvettew78

New Member
Joined
Aug 23, 2020
Messages
34
Platform
  1. Windows
  2. Web
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Aug 23, 2020
Messages
34
Platform
  1. Windows
  2. Web
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
11,914
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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.
VBA Code:
OR(AND(OR(E3={"Cash","Finance"}),E4="Residential",OR(E5={"Single","Three"})),AND(OR(E3={"Cash","Finance"}),E4="Commercial",E5="Three"))
VBA Code:
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.
VBA Code:
=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 o_O
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,391
Members
414,063
Latest member
N_Bates

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
Top