Solved

# Avoid #N/A in an Index Match Formula

Posted on 2013-01-10
431 Views
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
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

LVL 23

Accepted Solution

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

ID: 38764609
Perfect!
0

## Featured Post

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…
###### Suggested Courses
Course of the Month5 days, 9 hours left to enroll