Vlookup and #N/A

[Kayslover]

Hi,

I have 12 workbooks called 01 Jan, 02 Feb, 03 Mar etc which are created from a template. (i.e. one for each month of the year).

Within the workbook I have individual sheets called Sheet1 thru to Sheet5 which hold weekly dates for the individual weeks in the month. Sheet1 always starts of being the first Sunday of month, sheet2 will be the next Sunday within that month etc.

I then have a sheet called “Monthly Totals” which by using the following array formula extracts details from Sheet1, Sheet2 etc. The formula is:-

=VLOOKUP($A2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$4:$V$10"),$A2)>0),0))&"'!$B$4:$V$10"),2,FALSE)

and it is present rows B2 to A32 in columns B to V.

Col A2 in Monthly Totals has a date starting as the first Sunday of the month, A3:A32 has the following formula:-

=IFERROR(IF(Formula!F$2-(A2+1)>=0, A2+1,""),"")

, where Formula!F$2 is the end of the month for the month in question.

The above formula will populate A2:A32 with just the date with the number of days in the month. Months like Feb and those that contain 30 days in the month, in Column A will only be populated with the exact number of days for the month

The problem I have, is as some months only have 30 days, the Vlookup reports #N/A for the 31st of those months and obviously February will report the 29th, 30th and 31st as #N/A.

In Row 33, I use the following formula:-

=SUMIF($A$2:$A$32,"<>",B2:B32)

to sum all the data for the month.

Is there any way to automate that for months with less than 31 days, the Vlookup in the appropriate rows for columns B to V either remove the formula or do not give #N/A? as it is hampering the formula

=SUMIF($A$2:$A$32,"<>",B2:B32)

to sum all the data for the month in row 33. People have to remember to remove the Vlookup in rows that do not have a date to get the totals in Row 33.

Thanks.

 

[sandy666]

maybe

=IFNA(your_formula,"")

 

[Kayslover]

Hi Sandy666,

Thank you for your quick response.

I have changed the Vlookup array formula in B31 and B32 to read

=VLOOKUP($A31,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$4:$V$10"),$A31)>0),0))&"'!$B$4:$V$10"),2,FALSE),"")
=VLOOKUP($A32,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$4:$V$10"),$A32)>0),0))&"'!$B$4:$V$10"),2,FALSE),"")

and I now get #Name? in B31 and B32.

If I insert your recommeded formula in B33 (Total Line) as

=IFNA(SUMIF($A$2:$A$32,"<>",B2:B32),"")

, B33 just goes blank.

As I doing something obviously wrong?

 

[sandy666]

'or do not give #N/A?

this is what doing IFNA() for #N/A "error", giving blank IFNA(formula, "") or whatever you put here IFNA(formula, "whatever")

IFNA function - Microsoft Support

Syntax: IFNA(value, value_if_na)

support.microsoft.com

 

[Kayslover]

Sandy666,

I have changed B31 and B32 to

=IFNA(VLOOKUP($A31,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$4:$V$10"),$A31)>0),0))&"'!$B$4:$V$10"),2,FALSE),”0”)
=IFNA(VLOOKUP($A32,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$4:$V$10"),$A32)>0),0))&"'!$B$4:$V$10"),2,FALSE),”0”)

Please note that after the FALSE statement i have inserted a space character between the "".

Making the above change, now results in B31 and B32 displaying #NAME?.

I thought that by putting a space between the "" would result in formula to change to a space and deleting the formula?

 

[Kayslover]

Sandy666,

Please ignore the last message, I had typed it incorrectly.

The message should have read:-

have changed B31 and B32 to

=IFNA(VLOOKUP($A31,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$4:$V$10"),$A31)>0),0))&"'!$B$4:$V$10"),2,FALSE),”0”)
=IFNA(VLOOKUP($A32,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$B$4:$V$10"),$A32)>0),0))&"'!$B$4:$V$10"),2,FALSE),”0”)

Making the above change, now results in B31 and B32 displaying #NAME?.

I thought that by putting a 0 (zero) between the "" would result in formula to be overwritten with a value of 0?

 

[sandy666]

so use IFERROR(formula, "") instead IFNA()

and instead of ” use "

How to correct a #NAME? error

The top reason why the #NAME? error appears in your formula is because there is a typo in the formula name. To avoid typos in formula names, use the Formula Wizard in Excel.

support.microsoft.com

 

[Kayslover]

I have tried IFERROR and that also returns #Name?. Please note I an tying the IFERROR as part of the array formula.

 

[sandy666]

have you corrected your error according to the second line in post #7 ?
and did you read third line in the same post?

 

[Kayslover]

Sandy666,

Apologies, I hadn't read post #7, and as you pointed out the error.

I have since corrected the formula (tried both the ISNA and IFERROR and both work) and bingo it works.

My sincere thanks for the assistance you have offered.