IF Statements How can I shorten this formula

[willro]

I have been trying to create a spreadsheet that will look up references in in multiple tables in the worrkbook. I have come to a problem with IF statements. I have reached the maximum IF's I can use, however I need to reference three more tables, as well as it needs to differentiate between males and females. In other words with respect to males and females I need it to reference two different sets of tables.....Please help. If you need more infoplease just let me know.

This is the formula:

=IF(BD4>8.99,VLOOKUP(BE4,Sheet1!AB4:AC51,2),IF(BD4>7.99,VLOOKUP(BE4,Sheet1!Y4:Z51,2),IF(BD4>7.49,VLOOKUP(BE4,Sheet1!V4:W51,2),IF(BD4>6.99,VLOOKUP(BE4,Sheet1!S4:T51,2),IF(BD4>6.49,VLOOKUP(BE4,Sheet1!P4:Q51,2),IF(BD4>5.99,VLOOKUP(BE4,Sheet1!M4:N51,2),IF(BD4>5.49,VLOOKUP(BE4,Sheet1!J4:K51,2),0)))))))

 

[wsjackman]

it doesn't shorten it, but you can use more than 7 ifs as long as you don't embed them all

your formula could be converted to

= IF(BD4>5.99,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
IF(BD4>8.99,<o></o>
VLOOKUP(BE4,Sheet1!AB4:AC51,2),<o></o>
IF(BD4>7.99,<o></o>
VLOOKUP(BE4,Sheet1!Y4:Z51,2),<o></o>
IF(BD4>7.49,<o></o>
VLOOKUP(BE4,Sheet1!V4:W51,2),<o></o>
IF(BD4>6.99,<o></o>
VLOOKUP(BE4,Sheet1!S4:T51,2),<o></o>
IF(BD4>6.49,<o></o>
VLOOKUP(BE4,Sheet1!P4:Q51,2),<o></o>
VLOOKUP(BE4,Sheet1!M4:N51,2)))))),<o></o>
IF(BD4>5.49,VLOOKUP(BE4,Sheet1!J4:K51,2),
if
if
))))

would still work

or you could make a lookup table using bd4 as your lookup value and set your male female as columns 2 or three

 

[willro]

I appreciate your attempt on the formula, however there is a problem with it. When I input it into the cell excel says that there is an error with it. How can I fix it???

 

[wsjackman]

probably there is a parenthesis error I did not verify I had the right number of () for everything. Also did you add the appropriate ifs?

did it give a hint as to where the error was and what it was or just one of everything highlighted.

I often have problems copying formulas from the forum and pasting into excel. Most of the time I must at least replace the equal sign and a lot of times remerge formulas which paste into several cells. Parenthesis disappear during the copy paste, and a lot of times other computer code shows up during the paste.

 

[Andrew Poulsom]

Make a table like this:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num>5.49</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5.99</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6.49</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6.99</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>7.49</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>7.99</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>8.99</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>18</TD></TR></TBODY></TABLE>

and name it Table using Insert|Name|Define.

Then you can use the formula:

=VLOOKUP(BE4,OFFSET(Sheet1!$J$4:$J$51,0,VLOOKUP(BD4,Table,2),,2),2)

 

[willro]

wsjackman: Thank you, it has worked. How ever I need to throw one more variable into this mix. Now I need for this formula to separate between boys and girls(I am a teacher). How would you propose I do this???

Andrew: I attempted your way with the chart however I don't think my limited knowledge could really comprehend how to do this.

This was the final outcome for the function:
=IF(BD4>5.99,
IF(BD4>8.99,
VLOOKUP(BE4,Sheet1!AB4:AC51,2),
IF(BD4>7.99,
VLOOKUP(BE4,Sheet1!Y4:Z51,2),
IF(BD4>7.49,
VLOOKUP(BE4,Sheet1!V4:W51,2),
IF(BD4>6.99,
VLOOKUP(BE4,Sheet1!S4:T51,2),
IF(BD4>6.49,
VLOOKUP(BE4,Sheet1!P4:Q51,2),
VLOOKUP(BE4,Sheet1!M4:N51,2)))))),
IF(BD4>5.49,VLOOKUP(BE4,Sheet1!J4:K51,2),
IF(BD4>4.99,VLOOKUP(BE4,Sheet1!G4:H51,2),
IF(BD4>4.49,VLOOKUP(BE4,Sheet1!D4:E51,2),IF(BD4>3.99,VLOOKUP(BE4,Sheet1!A4:B51,2)
)))))

 

[wsjackman]

what do you want done differently if it is girls or boys?

how do you designate which you are working with?

 

[willro]

There are two sets of charts, one for boys and one for girls. The function you previously helped me with is essentiall just for boys I will create another function for girls, how can I account for them in the same cell.
As far as designation I can I am open to suggestion.

 

[willro]

I am also curious if I can protect specific cells in a workbook??

 

[wsjackman]

one method would be to make a similar formula for girls if this one is for boys

set up a column to indicate boys or girls

maybe bc

the formula would then be

=if(bc4="B",boys formula,girls formula)

I like andrew's table but am not sure it would meet your needs, as I assume BE will be different for each row