need help please....

[mrobin]

I am trying to get a formula correct, I need to do a vlookup - the results will be either a yes or a no. Frm that yes or no I want to have the cell show the yes as "Best Practice" and the No as 25% Savings - but I cannot get it right with using both vlookup and the if functions in the same cell.
eg:
=(VLOOKUP($K$4,'OLD Monthly Report '!$A$2:$AC$5146,13,FALSE))(if(k19,"Best Practice","25% Savings)

<TABLE style="WIDTH: 236pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=315 border=0 x:str><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><TBODY><TR style="HEIGHT: 33.75pt; mso-height-source: userset" height=45><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 33.75pt; BACKGROUND-COLOR: white" width=91 height=45> </TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=108>Best Practice or 25% Savings:</TD><TD class=xl68 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399 0.5pt solid; BORDER-LEFT: #333399 0.5pt solid; WIDTH: 87pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: silver" width=116>Yes</TD></TR></TBODY></TABLE>

I want the Yes in the grey box to change to Best Practice - or if the vlookup gives a No, I want the words 25% savings to show.

Can anyone help please ??

 

[T. Valko]

I am trying to get a formula correct, I need to do a vlookup - the results will be either a yes or a no. Frm that yes or no I want to have the cell show the yes as "Best Practice" and the No as 25% Savings - but I cannot get it right with using both vlookup and the if functions in the same cell.
eg:
=(VLOOKUP($K$4,'OLD Monthly Report '!$A$2:$AC$5146,13,FALSE))(if(k19,"Best Practice","25% Savings)

<TABLE style="WIDTH: 236pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=315 border=0 x:str><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><TBODY><TR style="HEIGHT: 33.75pt; mso-height-source: userset" height=45><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 33.75pt; BACKGROUND-COLOR: white" width=91 height=45></TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 81pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=108>Best Practice or 25% Savings:</TD><TD class=xl68 style="BORDER-RIGHT: #333399 0.5pt solid; BORDER-TOP: #333399 0.5pt solid; BORDER-LEFT: #333399 0.5pt solid; WIDTH: 87pt; BORDER-BOTTOM: #333399 0.5pt solid; BACKGROUND-COLOR: silver" width=116>Yes</TD></TR></TBODY></TABLE>

I want the Yes in the grey box to change to Best Practice - or if the vlookup gives a No, I want the words 25% savings to show.

Can anyone help please ??

Maybe this...

=IF(VLOOKUP($K$4,'OLD Monthly Report '!$A$2:$AC$5146,13,0)="Yes","Best Practice","25% Savings)

 

[mrobin]

I get an error message, " The formula you typed contains an error" ???

 

[T. Valko]

I get an error message, " The formula you typed contains an error" ???

Ooops!

I left out a closing quote after savings.

Try this one:

=IF(VLOOKUP($K$4,'OLD Monthly Report'!$A$2:$AC$5146,13,0)="Yes","Best Practice","25% Savings")

 

[mrobin]

Sorry, but now all I get is #N/A in the cell - regardless of the response being a yes or a no......

 

[T. Valko]

Sorry, but now all I get is #N/A in the cell - regardless of the response being a yes or a no......

OK, #N/A means the lookup function could not find the lookup value in the lookup table.

In other words, whatever is in K4 can not be found in 'OLD Monthly Report'!A2:A5146.

 

[mrobin]

I can see it in the table, so I know it is there..... if i do a standard vlookup I get a yes response (which is what is expected)....

 

[T. Valko]

I can see it in the table, so I know it is there..... if i do a standard vlookup I get a yes response (which is what is expected)....

That formula is already doing a "standard vlookup". All it's doing is testing the result of the lookup.

If this works:

=VLOOKUP($K$4,'OLD Monthly Report'!$A$2:$AC$5146,13,0)

Then this should also work:

=IF(VLOOKUP($K$4,'OLD Monthly Report'!$A$2:$AC$5146,13,0)="Yes","Best Practice","25% Savings")

 

[mrobin]

Thanks, not sure what was going on ... so I closed the spreadsheet and tried it again... all works great now.... Thanks so much...

 

[T. Valko]

Thanks, not sure what was going on ... so I closed the spreadsheet and tried it again... all works great now.... Thanks so much...

Good deal. Thanks for the feedback!