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

ceytl

Board Regular
Joined
Jun 6, 2009
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe
Rich (BB code):
=IFNA(your_formula, 0)
 
Last edited by a moderator:
Solution

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
Joined
Feb 28, 2002
Messages
1,348
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,407
Messages
5,624,587
Members
416,036
Latest member
eloisa manzanarez

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top