IF=IFERROR(VLOOKUP

[jgarvey23]

Hi All,

My below code performs a Vlookup based on an IF statement then adds 1 to the prior day business sheet if found. Except, if not found, it returns an #N/A & I have to manually change every cell to 1.

I've tried everything e.g =IF(ISERROR(VLOOKUP, =IF(ISERROR(VLOOKUP.. No success.

Any ideas and/or solutions would help!

'Days Vlookup
    Range("E2").Formula = _
        "=IF(VLOOKUP(C[-2],'[PBDunkAcc.xlsx]Shell'!C3:C5,3,FALSE),VLOOKUP(C[-2],'[PBDunkAcc.xlsx]Shell'!C3:C5,3,FALSE)+1,1)"
    Range("E2").Select

 

[SkipVought]

Hi

Use IFERROR() assuming that you have Excel version 2007 or greater.

[tt]
=IFERROR(Your vlookup,Your error return value)
[/tt]

 

[jgarvey23]

Skip,

My current formula

,'[PBDunkAcc.xlsx]Shell'!C3:C5,3,FALSE),VLOOKUP(C[-2],'[PBDunkAcc.xlsx]Shell'!C3:C5,3,FALSE)+1,1)"]

will always increase the prior day value by 1 if present. Except, the IFERROR option won't increase by 1, only change #N/A..

Do you know a way to build that functionality in or another method to keep the structure of my vlookup to increase the value if found & if not then put 1.

e.g.. Yesterdays spreadsheet had 15 for Security 2323232, this morning it will show 16 (Yesterday +1). I need the new formula/ method to do the same plus change errors to 1....

 

[SkipVought]

Well if you can't find it how can you add 1 to nothing?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jgarvey23]

ha, you got me there....

 

[SkipVought]

It logically seems that if the lookup returns N/A, the PREVIOUS cell's value might be returned.

But then I can't see your sheet/data, so that might be an incorrect assumption.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]