INDEX MATCH Not returing 0 (zero).

[KML1976]

I have a datasheet, I need to return the value from column D based on criteria in 2 other columns (A and B).

I keep getting blank cell when it should be returning 0.

=INDEX(Data!D1:D1481,MATCH(1,($A7=Data!A1:A1481)*($A8=Data!B1:B1481),0))

What do I need to do to make the formula return the 0?

Thanks in advance.
Kerry

[FDibbins]

Hi, welcome to the forum

If is returning nothing, maybe there is nothing to return?

perhaps...
=IF(INDEX(Data!D1:D1481,MATCH(1,($A7=Data!A1:A1481)*($A8=Data!B1:B1481),0))="","",=INDEX(Data!D1:D1481,MATCH(1,($A7=Data!A1:A1481)*($A8=Data!B1:B1481),0)))

[TMS]

I think that is an Array Formula and would need to be committed with Ctrl-Shift-Enter rather than just Enter.


Regards, TMS

[Aardigspook]

Is the sheet set up to display zeroes?
Go to File > Options > Advanced, scroll down until you find 'Display options for this worksheet', check if 'Show a zero in cells that have zero value' is ticked - if not, then tick it.
(The option might be somewhere slightly different for you in Excel 2013 - that's where it is in 2010.)


Edit: It's more likely that TMS's suggestion above is the solution you need.

[FDibbins]

I think Trevor has hit it, there

[TMS]

Well, CSE certainly works for me with a very, very simple test case.

Regards, TMS

[KML1976]

Thank you for your reply. It is already entered as an array formula, I just forgot to add in the {} when I pasted the formula above.

[KML1976]

Workbook is also set to show zeros.

On the sheet with my data in, I have used filters to manually find if there is something to return and there is definitely 0.
I must return the 0, I am unable to leave it blank because some of my results will be blank and we need to know the difference.

[hemesh]

you dont need to type curly braces by your self in the formula provided by Fdibbins
fdibbins provided
=IF(INDEX(Data!D1:D1481,MATCH(1,($A7=Data!A1:A1481)*($A8=Data!B1:B1481),0))="","",=INDEX(Data!D1:D1481,MATCH(1,($A7=Data!A1:A1481)*($A8=Data!B1:B1481),0)))
At the same time I think you need to remove = sign in red colour provided by Fdibbins


you need to copy paste above then hold control and shift together and hit enter instead of just Enter

Curly braces will come automatically confirming the entry of formula as array.

[KML1976]

Just to confirm, I haven't been typing curly brackets in myself, I've been entering it as array formula with Ctrl Shift Enter.

However, when I copied the from the formula bar and pasted here the curly brackets don't copy, so I just meant i'd not added them to the formula here to show people it was an array.

[TMS]

If you are getting a "blank" cell, I suspect the cell has a space in it. If you match on the values and the cell to be returned has a zero (0) OR is (truly) blank/empty, you would get a zero returned.

[TMS]

Suggest you post a sample workbook.

[KML1976]

I have attached the formula and data sheet so you can take a look (if you don't mind).
If I change the 0 to any other number then that number is returned by the formula, but just zero is not!

Thank you.


index match problem.xlsx

[TMS]

You have a custom number format, 0;-0;;@, which hides zero value

[KML1976]

ah ok...can't believe I didn't check that!
brilliant thanks

[TMS]

You're welcome.




If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.