Solved

Avoid #N/A in an Index Match Formula

Posted on 2013-01-10
2
387 Views
Last Modified: 2013-01-10
Hi!
I have the following formula that looks for values in a separate worksheet, whenever it doesn't find a value it places the #N/A symbol in the cell. Since I will be charting this information how can I get the cell to be left blank if nothing is captured ?

=INDEX('Act vs Bud'!$I$2:$ES$401,MATCH($A9,'Act vs Bud'!$A$2:$A$401,0),MATCH(B$1,'Act vs Bud'!$I$2:$ES$2,0))
0
Comment
Question by:joeserrone
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
Comment Utility
You can wrap it in an IFERROR()

eg

=IFERROR(INDEX('Act vs Bud'!$I$2:$ES$401,MATCH($A9,'Act vs Bud'!$A$2:$A$401,0),MATCH(B$1,'Act vs Bud'!$I$2:$ES$2,0)),"")
0
 

Author Closing Comment

by:joeserrone
Comment Utility
Perfect!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now