VLOOKUP if value exists, avoid #N/A

[simon551]

Hi,
I'm trying to pull values from a table that match an array of dates. If there is a date listed on the value table that matches my date, I'm ok, but if not I get the #N/A. Any help is appreciated.

=IF( VLOOKUP(A5,'Validation Sales'!A6:H1003,8,FALSE), VLOOKUP(A5,'Validation Sales'!A6:H1003,8,FALSE), IF(VLOOKUP(A5,'Validation Sales'!A6:H1003,3,FALSE), VLOOKUP(A5,'Validation Sales'!A6:H1003,3,FALSE),0))

 

[SkipVought]

if you have Excel 2007+ then...
[tt]
=iferror(VLOOKUP(A5,'Validation Sales'!A6:H1003,8,FALSE),0)
[/tt]
otherwise for 97-2003...
[tt]
=if(isna(match(A5,'Validation Sales'!A6:A1003,0),0,VLOOKUP(A5,'Validation Sales'!A6:H1003,8,FALSE))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MazeWorX]

You could use the COUNTIF function to check the existence of the Vlookup data or conditional formatting can solve the issue as well. Be wary of double Vlookups with an if statement it can cause major issue when dealing with a lot of data

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[SkipVought]

BOTH Ifs use the SAM LOOKUP VALUE!!! Therefore the second if is totally unnecessary.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MazeWorX]

sorry I meant to say IF with isna and 2 lookups is the least effective ... which is a solution many may consider better yet put the formula in another cell and reference it with =IF(ISNA(A1),0,A1) assuming the formula is in A1 then hide the column

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>