#N/A when using a IF Statement and Index Match

ceytl

Board Regular
If I don't have a the correct value in D32:D45 using formula =IF(AND(A25="2020D",F25+INDEX(\$C\$32:\$C\$45,MATCH(A25,\$D\$32:\$D\$45,0))>=6),6-2,F25) it comes back #N/A.

How do I fix the #N/A

I'm thinking I need to have it return 0 if no value and then it can add to cell F25.

Thanks!
Test.xlsx
ABCDEFG
23Part No.DescriptionCode Retail Cost Order QtyShip Qty
242020DAirplaneAZ\$2,000.00\$850.0054
252020ZAirplaneAZ\$2,000.00\$850.002#N/A
26
27
28
29
30
31QTY Part#
322 2020D
332
34
35
36
37
38
39
40
41
42
43
44
45
Sheet1
Cell Formulas
RangeFormula
G24:G25G24=IF(AND(A24="2020D",F24+INDEX(\$C\$32:\$C\$45,MATCH(A24,\$D\$32:\$D\$45,0))>=6),6-2,F24)

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

sandy666

Banned - Rules violations
maybe
Rich (BB code):
``=IFNA(your_formula, 0)``

Last edited by a moderator:

ceytl

Board Regular
Last edited by a moderator:

sandy666

Banned - Rules violations
You are welcome & thanks for the feedback

btw. you marked as solution your own post which is not true in this case

Last edited:

smozgur

BatCoder
It worked! Thank you!

@ceytl - Although we closely monitor and moderate the marked solutions, in your future questions, please mark the solution which answers your question instead of your own post - unless you found and posted your own solution. This way, it will help future visitors who might have the same question. In fact, you might also consider revisiting your old questions and mark the solution posts to help future visitors. You can find the list of your old questions by using the following link:

As I mentioned, we are already moderating the marked solutions. Therefore, I already switched the marked solution as necessary in this thread. There is no further action to do on this question.

Replies
3
Views
36
Replies
6
Views
66
Replies
5
Views
101
Replies
1
Views
81
Replies
2
Views
379

1,127,844
Messages
5,627,219
Members
416,230
Latest member
jdaitchman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back