I need Vlookup not to show #N/A in cells where the source file doesn't yet exist.

[SandsB]

I have rows of data from a VLOOOKUP that gets data from daily source files except the files aren't created on Sundays or for the dates we haven't reached yet. (So on 8/1 I get data for 8/1 but every other day for the current month shows #N/A)

=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-01-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-02-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-03-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
=VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-04-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29
etc

I have monthly tabs to get all this data and it all works fine but every month I have to edit the file and remove the formulas for Sundays and even then I still have an ugly report for the days still in the future. How can I code the same formula so if the source file doesn't exist yet the formula just returns a blank?

 

[Georgiboy]

One option could be to wrap it with IfError:

=IFERROR(VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-01-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"")

 

[hrayani]

try

=IFERROR(VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-01-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"")

 

[Alex Blakenburg]

The IfError version is often used for "VLookup not found" but in your case you are also multiplying by 29 and it will also hide an error where the VLookup returns a text value. If you don't want that then use IFNA instead.

=IFNA(VLOOKUP($A6,'J:\InputFiles\[Daily_Stats 08-01-2022.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"")