Iserror with if(or & Vlookups

[Milodie66]

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))

 

[T. Valko]

=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($G39,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.

 

[Milodie66]

=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),VLOOKUP(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]

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($G39)={"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)?

 

[Milodie66]

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),VLOOKUP(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]

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.