Solved

Avoid #N/A in an Index Match Formula

Posted on 2013-01-10
2
431 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 38764491
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
ID: 38764609
Perfect!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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