| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Iserror with if(or & Vlookups
I am having a problem placing in an iserror with the following formula. I
have used iserror before to do if functions if(iserror(vlookup)),0,vlookup)) etc... but cannot get it to work with this many functions. I would like the formula to return a zero if the lookup is not present. I have tried to place the iserror directly after the if( and complete the rest of the formula with ,0,or(repeat formula). I'm pulling out my hair, which is bad, because I'm already half bald. =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3"),VLOOKUP( LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE),VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Iserror with if(or & Vlookups
=IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3")
You can reduce that to: =IF(OR(LEFT($G39)={"2","3"}) For the error trap: =IF(OR(LEFT($G39)={"2","3"}),IF(ISNA(MATCH(LEFT($G 39,6),'MONTH-YTD Balance Sheet'!$B$8:$B$1000,0)),0,................),"") You can save some more keystrokes by using a more compact sheet name without any spaces: MYTDBal (or something like that) You can save a few keystrokes by replacing every instance of FALSE with 0. You could also save some keystrokes by using an intermediate cell to hold: =LEFT($G39,6) Instead of repeating it all those times in the formula: A1: =LEFT($G39,6) Then, replace every instance of LEFT($G39,6) with $A1. -- Biff Microsoft Excel MVP "Milodie66" wrote in message ... I am having a problem placing in an iserror with the following formula. I have used iserror before to do if functions if(iserror(vlookup)),0,vlookup)) etc... but cannot get it to work with this many functions. I would like the formula to return a zero if the lookup is not present. I have tried to place the iserror directly after the if( and complete the rest of the formula with ,0,or(repeat formula). I'm pulling out my hair, which is bad, because I'm already half bald. =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3"),VLOOKUP( LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE),VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Iserror with if(or & Vlookups
=IF(OR(LEFT($G39)={"2","3"}),VLOOKUP(LEFT($G39,6), BS!$B$8:$P$1000,BS!$B$4,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0),VL OOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$4,0))
T-Thanks for the keystroke advice, made things considerably easier to read. I'm still having some issues with the formula. If you notice, I am looking up 2 seperate values in an "ending" balance minus a "beginning" balance to get my difference. If the number begins with a 2 or 3, I want the formula to perform a lookup finding Beginning-Ending. If it does not, I would like it to subtract Ending from Beginning. "T. Valko" wrote: =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3") You can reduce that to: =IF(OR(LEFT($G39)={"2","3"}) For the error trap: =IF(OR(LEFT($G39)={"2","3"}),IF(ISNA(MATCH(LEFT($G 39,6),'MONTH-YTD Balance Sheet'!$B$8:$B$1000,0)),0,................),"") You can save some more keystrokes by using a more compact sheet name without any spaces: MYTDBal (or something like that) You can save a few keystrokes by replacing every instance of FALSE with 0. You could also save some keystrokes by using an intermediate cell to hold: =LEFT($G39,6) Instead of repeating it all those times in the formula: A1: =LEFT($G39,6) Then, replace every instance of LEFT($G39,6) with $A1. -- Biff Microsoft Excel MVP "Milodie66" wrote in message ... I am having a problem placing in an iserror with the following formula. I have used iserror before to do if functions if(iserror(vlookup)),0,vlookup)) etc... but cannot get it to work with this many functions. I would like the formula to return a zero if the lookup is not present. I have tried to place the iserror directly after the if( and complete the rest of the formula with ,0,or(repeat formula). I'm pulling out my hair, which is bad, because I'm already half bald. =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3"),VLOOKUP( LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE),VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Iserror with if(or & Vlookups
I'm still having some issues with the formula
What kind of issues? The formula is syntactically correct so it's not the formula (unless the lookup_value isn't found in which case you'll get a #N/A error). You could save a few more keystrokes by replacing the col_index_num cell ref with the IF(OR(....)): =VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,IF(OR(LEFT($ G39)={"2","3"}),BS!$B$4,BS!$B$3),0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,IF(OR(LEFT($G 39)={"2","3"}),BS!$B$3,BS!$B$4),0) What does LEFT($G39,6) return? Is this a number or an alpha-numeric string? Is the first column of your lookup table, BS!$B$8:$B$1000 the same data type as LEFT($G39,6)? -- Biff Microsoft Excel MVP "Milodie66" wrote in message ... =IF(OR(LEFT($G39)={"2","3"}),VLOOKUP(LEFT($G39,6), BS!$B$8:$P$1000,BS!$B$4,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0),VL OOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$4,0)) T-Thanks for the keystroke advice, made things considerably easier to read. I'm still having some issues with the formula. If you notice, I am looking up 2 seperate values in an "ending" balance minus a "beginning" balance to get my difference. If the number begins with a 2 or 3, I want the formula to perform a lookup finding Beginning-Ending. If it does not, I would like it to subtract Ending from Beginning. "T. Valko" wrote: =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3") You can reduce that to: =IF(OR(LEFT($G39)={"2","3"}) For the error trap: =IF(OR(LEFT($G39)={"2","3"}),IF(ISNA(MATCH(LEFT($G 39,6),'MONTH-YTD Balance Sheet'!$B$8:$B$1000,0)),0,................),"") You can save some more keystrokes by using a more compact sheet name without any spaces: MYTDBal (or something like that) You can save a few keystrokes by replacing every instance of FALSE with 0. You could also save some keystrokes by using an intermediate cell to hold: =LEFT($G39,6) Instead of repeating it all those times in the formula: A1: =LEFT($G39,6) Then, replace every instance of LEFT($G39,6) with $A1. -- Biff Microsoft Excel MVP "Milodie66" wrote in message ... I am having a problem placing in an iserror with the following formula. I have used iserror before to do if functions if(iserror(vlookup)),0,vlookup)) etc... but cannot get it to work with this many functions. I would like the formula to return a zero if the lookup is not present. I have tried to place the iserror directly after the if( and complete the rest of the formula with ,0,or(repeat formula). I'm pulling out my hair, which is bad, because I'm already half bald. =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3"),VLOOKUP( LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE),VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Iserror with if(or & Vlookups
What does LEFT($G39,6) return? Is this a number or an alpha-numeric string?
It is 6 digit GL #, followed by a description Is the first column of your lookup table, BS!$B$8:$B$1000 the same data type as LEFT($G39,6)? Yes When the GL code is present on the BS tab, the following formula works exactly how I want it to and returns a value. It performs the value variances (beg-end for #'s starting with 2 or 3, end-beg for the remainder). It is only when the value is not present that it is returning the #NA's. That is what I want to correct. Your formula in the messages below may do that, but I may not be excel savvy enough to make it work correctly. What do I add/change on this formula to make it work just how it is now, but get rid of the NA's. Sorry if I'm too dense to figure out that you already told me how to do it. =IF(OR(LEFT($G39)={"2","3"}),VLOOKUP(LEFT($G39,6), BS!$B$8:$P$1000,BS!$B$4,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0),VL OOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$4,0)) "T. Valko" wrote: I'm still having some issues with the formula What kind of issues? The formula is syntactically correct so it's not the formula (unless the lookup_value isn't found in which case you'll get a #N/A error). You could save a few more keystrokes by replacing the col_index_num cell ref with the IF(OR(....)): =VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,IF(OR(LEFT($ G39)={"2","3"}),BS!$B$4,BS!$B$3),0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,IF(OR(LEFT($G 39)={"2","3"}),BS!$B$3,BS!$B$4),0) What does LEFT($G39,6) return? Is this a number or an alpha-numeric string? Is the first column of your lookup table, BS!$B$8:$B$1000 the same data type as LEFT($G39,6)? -- Biff Microsoft Excel MVP "Milodie66" wrote in message ... =IF(OR(LEFT($G39)={"2","3"}),VLOOKUP(LEFT($G39,6), BS!$B$8:$P$1000,BS!$B$4,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0),VL OOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$4,0)) T-Thanks for the keystroke advice, made things considerably easier to read. I'm still having some issues with the formula. If you notice, I am looking up 2 seperate values in an "ending" balance minus a "beginning" balance to get my difference. If the number begins with a 2 or 3, I want the formula to perform a lookup finding Beginning-Ending. If it does not, I would like it to subtract Ending from Beginning. "T. Valko" wrote: =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3") You can reduce that to: =IF(OR(LEFT($G39)={"2","3"}) For the error trap: =IF(OR(LEFT($G39)={"2","3"}),IF(ISNA(MATCH(LEFT($G 39,6),'MONTH-YTD Balance Sheet'!$B$8:$B$1000,0)),0,................),"") You can save some more keystrokes by using a more compact sheet name without any spaces: MYTDBal (or something like that) You can save a few keystrokes by replacing every instance of FALSE with 0. You could also save some keystrokes by using an intermediate cell to hold: =LEFT($G39,6) Instead of repeating it all those times in the formula: A1: =LEFT($G39,6) Then, replace every instance of LEFT($G39,6) with $A1. -- Biff Microsoft Excel MVP "Milodie66" wrote in message ... I am having a problem placing in an iserror with the following formula. I have used iserror before to do if functions if(iserror(vlookup)),0,vlookup)) etc... but cannot get it to work with this many functions. I would like the formula to return a zero if the lookup is not present. I have tried to place the iserror directly after the if( and complete the rest of the formula with ,0,or(repeat formula). I'm pulling out my hair, which is bad, because I'm already half bald. =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3"),VLOOKUP( LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE),VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Iserror with if(or & Vlookups
Add this to the beginning of the formula:
=IF(COUNTIF(BS!$B$8:$B$1000,LEFT($G39,6))=0,"", And add another closing ")" to the very end of the formula. -- Biff Microsoft Excel MVP "Milodie66" wrote in message ... What does LEFT($G39,6) return? Is this a number or an alpha-numeric string? It is 6 digit GL #, followed by a description Is the first column of your lookup table, BS!$B$8:$B$1000 the same data type as LEFT($G39,6)? Yes When the GL code is present on the BS tab, the following formula works exactly how I want it to and returns a value. It performs the value variances (beg-end for #'s starting with 2 or 3, end-beg for the remainder). It is only when the value is not present that it is returning the #NA's. That is what I want to correct. Your formula in the messages below may do that, but I may not be excel savvy enough to make it work correctly. What do I add/change on this formula to make it work just how it is now, but get rid of the NA's. Sorry if I'm too dense to figure out that you already told me how to do it. =IF(OR(LEFT($G39)={"2","3"}),VLOOKUP(LEFT($G39,6), BS!$B$8:$P$1000,BS!$B$4,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0),VL OOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$4,0)) "T. Valko" wrote: I'm still having some issues with the formula What kind of issues? The formula is syntactically correct so it's not the formula (unless the lookup_value isn't found in which case you'll get a #N/A error). You could save a few more keystrokes by replacing the col_index_num cell ref with the IF(OR(....)): =VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,IF(OR(LEFT($ G39)={"2","3"}),BS!$B$4,BS!$B$3),0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,IF(OR(LEFT($G 39)={"2","3"}),BS!$B$3,BS!$B$4),0) What does LEFT($G39,6) return? Is this a number or an alpha-numeric string? Is the first column of your lookup table, BS!$B$8:$B$1000 the same data type as LEFT($G39,6)? -- Biff Microsoft Excel MVP "Milodie66" wrote in message ... =IF(OR(LEFT($G39)={"2","3"}),VLOOKUP(LEFT($G39,6), BS!$B$8:$P$1000,BS!$B$4,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0),VL OOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$3,0)-VLOOKUP(LEFT($G39,6),BS!$B$8:$P$1000,BS!$B$4,0)) T-Thanks for the keystroke advice, made things considerably easier to read. I'm still having some issues with the formula. If you notice, I am looking up 2 seperate values in an "ending" balance minus a "beginning" balance to get my difference. If the number begins with a 2 or 3, I want the formula to perform a lookup finding Beginning-Ending. If it does not, I would like it to subtract Ending from Beginning. "T. Valko" wrote: =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3") You can reduce that to: =IF(OR(LEFT($G39)={"2","3"}) For the error trap: =IF(OR(LEFT($G39)={"2","3"}),IF(ISNA(MATCH(LEFT($G 39,6),'MONTH-YTD Balance Sheet'!$B$8:$B$1000,0)),0,................),"") You can save some more keystrokes by using a more compact sheet name without any spaces: MYTDBal (or something like that) You can save a few keystrokes by replacing every instance of FALSE with 0. You could also save some keystrokes by using an intermediate cell to hold: =LEFT($G39,6) Instead of repeating it all those times in the formula: A1: =LEFT($G39,6) Then, replace every instance of LEFT($G39,6) with $A1. -- Biff Microsoft Excel MVP "Milodie66" wrote in message ... I am having a problem placing in an iserror with the following formula. I have used iserror before to do if functions if(iserror(vlookup)),0,vlookup)) etc... but cannot get it to work with this many functions. I would like the formula to return a zero if the lookup is not present. I have tried to place the iserror directly after the if( and complete the rest of the formula with ,0,or(repeat formula). I'm pulling out my hair, which is bad, because I'm already half bald. =IF(OR(LEFT($G39,1)="2",LEFT($G39,1)="3"),VLOOKUP( LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE),VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$3,FALSE)-VLOOKUP(LEFT($G39,6),'MONTH-YTD Balance Sheet'!$B$8:$P$1000,'MONTH-YTD Balance Sheet'!$B$4,FALSE)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dependent vlookups - nested vlookups (maybe) | Excel Worksheet Functions | |||
| ISERROR | Excel Worksheet Functions | |||
| IF ISERROR | Excel Discussion (Misc queries) | |||
| ISERROR | Excel Worksheet Functions | |||
| iserror | Excel Worksheet Functions | |||
Hybrid Mode
