Hello. I have a formula to return either 0 or the cell value * the corresponding match in Table 1:=IF(AND(ISNUMBER(MATCH(1, (Table1[Name adjusted]=A6)*(Table1[Leave Date]=$C$4), 0)), INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))=1), 0, IFERROR(C6*INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0)), "")) The formula works when all conditions are met, it will return 0. When conditions are not met, it is still returning #N/A despite the IFERROR applied. Can anyone please provide insight as to why the IFERROR component is not working? Please let me know if additional information is required.

MAVOTRP It seems like you're encountering issues with the logical test part of your formula, specifically the AND function, which checks multiple conditions simultaneously. Let's break down your formula and address the problem:The logical test in your formula is designed to check if there is a match for all three criteria in Table 1 on Sheet 2, and if the value in Column C is 100%. If these conditions are met, the formula returns 0; otherwise, it should return the value in C6 (Sheet 1) multiplied by the corresponding value in Table 1 Column C.Here's your logical test part:AND( ISNUMBER(MATCH(1, (Table1[Name adjusted]=A10)*(Table1[Leave Date]=$C$4), 0)), INDEX(Table1[Activity duration - For part-day availability], MATCH(A10&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))=1)Final Formula:=IF(AND(ISNUMBER(MATCH(1, (Table1[Name adjusted]=A10)*(Table1[Leave Date]=$C$4), 0)),INDEX(Table1[Activity duration - For part-day availability], MATCH(A10&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))=1),0,IF(ISERROR(C6*INDEX(Table1[Activity duration - For part-day availability],MATCH(A10&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))),"",C6*INDEX(Table1[Activity duration - For part-day availability],MATCH(A10&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))))To troubleshoot the issue, you can check the following:Verify the Data: Ensure that the data in Table 1 is structured correctly, and there are no formatting or data entry errors that might cause the formula to fail.Test Each Condition Separately: To isolate the problem, you can test each condition separately to see if it works as expected. For example, you can check if the MATCH function returns the correct row, if the conditions for Name adjusted, Leave Date, and Activity duration are met individually.Check for Data Mismatch: Ensure that there are no discrepancies between the data in Sheet 1 and Sheet 2. Even a small difference in formatting or data type can cause the formula to fail.Error Handling: Add error handling mechanisms such as IFERROR around specific parts of your formula to catch any potential errors and handle them appropriately. This can help in troubleshooting and identifying the root cause of the problem.Debugging: Use the built-in debugging tools in Excel, such as evaluating each part of the formula step by step or using the "Evaluate Formula" feature to identify where the problem lies.By systematically checking each component of your formula and verifying the data, you should be able to identify and resolve the issue with your logical test.

MAVOTRP Try this modification of your formula, maybe it will help you further with your plans. =IF(AND(ISNUMBER(MATCH(1, (Table1[Name adjusted]=A6)*(Table1[Leave Date]=$C$4), 0)), INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))=1), 0, IF(ISERROR(C6*INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0)))), "", C6*INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))) This modification checks if the result of the expression C6*INDEX(...) produces an error using ISERROR, and if it does, it returns "" (an empty string). Otherwise, it returns the result of the expression. This should prevent #N/A from being displayed when an error occurs. My answers are voluntary and without guarantee! Hope this will help you. Was the answer useful? Mark as best response and like it!This will help all forum participants.

Could you please share the workbook to find out where exactly this error is coming from.

MAVOTRP In your formula =IF( AND( ISNUMBER(MATCH(1, (Table1[Name adjusted]=A6)*(Table1[Leave Date]=$C$4), 0)), INDEX( Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0) )=1), 0, IFERROR( C6*INDEX( Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0) ), "") ) if first MATCH() returns #N/A, when INDEX() on top also returns #N/A, thus AND() as well. Result of =IF(#N/A, IFERROR(calculation, "")) will be #N/A. Aa variant you may wrap first INDEX by ISNUMBER, depend on what you'd like to receive.

Hi Sergei. Thank you for your response. I am getting an error saying I have too many arguments. Do you mind showing how to wrap the first INDEX by ISNUMBER? I may not be doing it correctly. Thank you.

IFERROR formula still returning #N/A | Microsoft Community Hub

11 Replies

SergeiBaklan
Diamond Contributor
Mar 17, 2024
MAVOTRP

In your formula

=IF( AND( ISNUMBER(MATCH(1, (Table1[Name adjusted]=A6)*(Table1[Leave Date]=$C$4), 0)), INDEX( Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0) )=1), 0, IFERROR( C6*INDEX( Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0) ), "") )

if first MATCH() returns #N/A, when INDEX() on top also returns #N/A, thus AND() as well. Result of =IF(#N/A, IFERROR(calculation, "")) will be #N/A.

Aa variant you may wrap first INDEX by ISNUMBER, depend on what you'd like to receive.
- MAVOTRP
  Copper Contributor
  Mar 17, 2024
  Hi Sergei. Thank you for your response. I am getting an error saying I have too many arguments. Do you mind showing how to wrap the first INDEX by ISNUMBER? I may not be doing it correctly. Thank you.
SandeepMarwal
Brass Contributor
Mar 17, 2024
Could you please share the workbook to find out where exactly this error is coming from.
- MAVOTRP
  Copper Contributor
  Mar 17, 2024
  I cannot share the workbook but this is the data I am working with:
  Sheet 1
  Column A Name
  Column C Numerical value (generally 1 or 0)
  
  Sheet 2
  Table 1
  Column A Name adjusted
  Column B Leave Date
  Column C Activity duration - For part-day availability
  
  The formula is checking all three columns in Table 1 on Sheet 2 for a match. If there is a match for all three, and the value in Column C is 100%, return 0. If it is NOT a match for all three, or Column C is not 100%, e.g., 25%, then return value in C6 (Sheet 1) * the corresponding value in Table 1 Column C (25% / 50% / 75%).
  
  The formula works fine when there is a match for all three, and the value in Column C is 100%, the formula returns 0. However, when it is NOT a match for all three or it is not 100%, it returns #N/A instead of returning the value in C6 (Sheet 1) * the corresponding value in Table 1 Column C (25% / 50% / 75%).
  - SandeepMarwal
    Brass Contributor
    Mar 18, 2024
    MAVOTRP
    
    Highlight this portion of your formula in formula bar:
    
    Press F9.
    
    Check what it is returning.
    
    if it is returning #N/A, then problem is with your logical test, not in the iferror part.
NikolinoDE
Platinum Contributor
Mar 14, 2024
MAVOTRP
Try this modification of your formula, maybe it will help you further with your plans.

=IF(AND(ISNUMBER(MATCH(1, (Table1[Name adjusted]=A6)*(Table1[Leave Date]=$C$4), 0)), INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))=1), 0, IF(ISERROR(C6*INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0)))), "", C6*INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0)))

This modification checks if the result of the expression C6*INDEX(...) produces an error using ISERROR, and if it does, it returns "" (an empty string). Otherwise, it returns the result of the expression. This should prevent #N/A from being displayed when an error occurs.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!
This will help all forum participants.
- MAVOTRP
  Copper Contributor
  Mar 17, 2024
  Hi Nikolino. I am trying your formula however I am getting the dialogue box saying There's a problem with this formula. I have tried moving one of the ) to the end and the formula is accepted, however it is still returning #N/A. Not sure if what I've done is correct see below:
  =IF(AND(ISNUMBER(MATCH(1, (Table1[Name adjusted]=A6)*(Table1[Leave Date]=$C$4), 0)), INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))=1), 0, IF(ISERROR(C6*INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))), "", C6*INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))))
  Any guidance would be appreciated. Thank you!
  - NikolinoDE
    Platinum Contributor
    Mar 17, 2024
    MAVOTRP
    It seems like there was a small typo in the formula you provided. The extra parenthesis at the end of the formula is causing the syntax error. Here's the corrected version of the formula:
    =IF(AND(ISNUMBER(MATCH(1, (Table1[Name adjusted]=A6)*(Table1[Leave Date]=$C$4), 0)), INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))=1), 0, IF(ISERROR(C6*INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))), "", C6*INDEX(Table1[Activity duration - For part-day availability], MATCH(A6&$C$4, Table1[Name adjusted]&Table1[Leave Date], 0))))
    In this corrected formula, I removed the extra parenthesis at the end so that the syntax is correct. Please try using this formula and see if it resolves the issue with the #N/A error.

Forum Discussion

IFERROR formula still returning #N/A

11 Replies

Resources