# index match , 0 instead of N/A when no result

1. ## index match , 0 instead of N/A when no result

Hello Forum

I have the below forumla if there is no result i get a N/A error which for what i usually use it for works for me, but i now need it to display a 0 instead of a N/A if there is no result.

Formula:
`Please Login or Register  to view this content.`

2. ## Re: index match , 0 instead of N/A when no result

You can do this:

=IFERROR(INDEX(D:D,MATCH(A1&B1,A:A&B:B,0)),0) Ctrl Shift Enter

or this non-array version:

=IFERROR(INDEX(D:D,INDEX(MATCH(A1&B1,A:A&B:B,0),0)),0)

One thing to note is not to use whole column references as this may slow down your workbook. Try to limit your ranges to a smaller range such as A\$2:A\$100000.

3. ## Re: index match , 0 instead of N/A when no result

After looking at your formula a little bit closer, there is probably a better way to do what you are trying to do.

A1&B1 will always be in A&B.

Your formula in post #1 will work the same as =D1

4. ## Re: index match , 0 instead of N/A when no result

IFERROR is likely your best option

5. ## Re: index match , 0 instead of N/A when no result

Helloo 63falcondude

The non-array version worked perfectly, the array version for some strange reason brought the result 0 for every entry.

Thanks for the help

6. ## Re: index match , 0 instead of N/A when no result

Glad to help. Thanks for the rep!

The array formula has to be entered using Ctrl Shift Enter instead of just Enter. If you do not enter an array formula with CSE, it will not work as intended.

That being said, I prefer to use a non-array formula over an array equivalent whenever possible.

I am still curious as to why you need an INDEX MATCH formula since A1 will always be in column A and B1 will always be in column B but you seem to be happy with what you have and that is what matters.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1