ExcelBanter - If/Vlookup override

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - If/Vlookup override (https://www.excelbanter.com/excel-worksheet-functions/232809-if-vlookup-override.html)

If/Vlookup override

I have a form that I am creating and need your assistance. Currently I have
the following formula and it is working perfectly.

=IF($J$7="Small",VLOOKUP(A13,'Property
List'!$F$2:$G$31,2,FALSE),IF($J$7="Medium",VLOOKUP (A13,'Property
List'!$F$2:$H$31,3,FALSE),IF($J$7="Large",VLOOKUP( Sales!A13,'Property
List'!$F$2:$I$31,4,FALSE))))

The only thing is that now I need to add two more variables and I am unsure
how to proceed. Regardless if $JS7 is Small, Medium or Large I need the
formula to override if either any of the following happens:

IF(E13="SC",5)
or
IF(E13="SNF",3)

Thanks.

If/Vlookup override

Don,
Thanks for your quick response but I can't get it to work. Not sure why to
use A13 in the formula.

Basically there is a field that is populated at the top of the form that
states if the property size is SMall, Medium and Large. Then they go into a
grid at the bottom were they need to put name, type of industry, rent, etc.
Depending on the size they selected at top there is going to be a commision
percentage populating into cell K13. The commision rates are on tab "Property
List" and range F2:I3.

If they happen to select the industry type on E13 (which is a drop down
menu) to be "SC" or "SNF" (and not the other options) then it shouldn't
matter if they are Small, Medium or Large and should automatically populate a
5 if "SC" or a 3 if "SNF"

Would this help?
Thanks a million for looking into this for me :)
Carolina

"Carolina" wrote:

I have a form that I am creating and need your assistance. Currently I have
the following formula and it is working perfectly.

=IF($J$7="Small",VLOOKUP(A13,'Property
List'!$F$2:$G$31,2,FALSE),IF($J$7="Medium",VLOOKUP (A13,'Property
List'!$F$2:$H$31,3,FALSE),IF($J$7="Large",VLOOKUP( Sales!A13,'Property
List'!$F$2:$I$31,4,FALSE))))

The only thing is that now I need to add two more variables and I am unsure
how to proceed. Regardless if $JS7 is Small, Medium or Large I need the
formula to override if either any of the following happens:

IF(E13="SC",5)
or
IF(E13="SNF",3)

Thanks.

If/Vlookup override

Don,
I had to leave the office and I am returning only until now (maternity
leave). I was unsuccesful with this suggestionbelow so it could be a matter
of me not explaining my idea correctly . Is there any way I can email you the
file I am working on and we can later post the ending result for others to
benefit from?

Thanks.
Carolina

"Don Guillett" wrote:

Misunderstood. Try this. You need to decide on sc and snf importance

=IF(E13="sc",5,IF(E13="snf",3,VLOOKUP(A13,Sheet1!E 1:X31,IF(J7="small",2,IF(J7="medium",3,IF(J7="larg e",4))),0)))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

Modify lookup range to suit

=VLOOKUP(A13,Sheet1!A1:X31,IF(E13="sc",4,IF(E13="s nf",3,IF(J7="small",2,IF(J7="medium",3,IF(J7="larg e",4))))),0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carolina" wrote in message
...
I have a form that I am creating and need your assistance. Currently I
have
the following formula and it is working perfectly.

=IF($J$7="Small",VLOOKUP(A13,'Property
List'!$F$2:$G$31,2,FALSE),IF($J$7="Medium",VLOOKUP (A13,'Property
List'!$F$2:$H$31,3,FALSE),IF($J$7="Large",VLOOKUP( Sales!A13,'Property
List'!$F$2:$I$31,4,FALSE))))

The only thing is that now I need to add two more variables and I am
unsure
how to proceed. Regardless if $JS7 is Small, Medium or Large I need the
formula to override if either any of the following happens:

IF(E13="SC",5)
or
IF(E13="SNF",3)

Thanks.