Index/Match "" vs 0 for blank cells

[tjlaser shepard]

What factors determine whether the index/match function combination to returns “” rather than “0” when the referenced cell is blank?

I set up a spreadsheet that tracks changes to expected project revenue by month. The reference table [NRSDATA] has project numbers as the rows [SREFROWS] and months as the columns [SREFCOLS]. If a project has no changes for a given month, that cell is left blank in the reference table. On another sheet, I list all the months in SREFROWS in column “A” and the user enters the project number in B1. Next to each month in in column B (starting with B3) I have the following formula:

=IFNA(INDEX(NSRDATA,MATCH($B$1,SREFROWS,0),MATCH(A3,SREFCOLS,0)),"")

If there is no change for that project in that month, the reference cell will be blank and this formula should return “”. Based on what the index/match formula returns, I have this formula in column E:

=IF(ISNUMBER(B3),1,"")

And then a macro that hides all the rows that have blank cells in column E.
All of this worked perfectly for the historical data, but all of the new projects I have added to the reference table since setting all of this up return “0” for every month where the reference is blank. Does anyone know why this would/could be the case?

Thanks

 

[pgc01]

Hi
Welcome to the board

Maybe the problem is the word "blank" in your post.

I don't understand exactly that word in your post.

It seems you are using the word blank for 2 different things:

- an empty cell (the cell has nothing)
- a cell with an empty string (the cells has a string with 0 characters)

In the first case it would return 0 and in the second case it would return "" (an empty string)

Please clarify.

 

[tjlaser shepard]

Thanks PGC. By blank cell, I mean cell with nothing in it. every cell in the reference table which is at the intersection of a project and a month with no activity in it is an empty cell. This is why I am puzzled by the fact that the newer projects are returning "0" for inactive months rather than "". I have run some tests in the reference table to compare the empty cells for the affected projects with empty cells in the projects that are functional. I could not find any discernable difference between the two; in all the examples I tried ISBLANK returned a TRUE value and LEN returned 0. Is there another test I should try? Based on your answer it sounds like the blank cells in the affected projects might have somehow ended up with empty strings in them. If that is the case, is there another way to clear out the string other than selecting the cell and hitting delete (which I have tried)? Thanks again.

 

[pgc01]

Hi

If ISBLANK() returns TRUE then the cell is empty.

In this case if all the cells are empty I don't know what could be the problem.

Can you post a small sample of data and the formula you use so that we can test?

 

[tjlaser shepard]

Hi,

Here is an example of what the "Sales REF" sheet looks like. I have color coded the named ranges and included a key:

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J
1		Mar-16	Apr-16	May-16			named		NSRDATA
2	961211-00	3617000					range		SREFCOLS
3							key		SREFROWS
4		SOV
5	961212-00		4000	91500
6			Design Only	Full Contract
7				Design add [$4,800], Mobilization [$23,000], VSC Installation [$63,700]
8	961213-00		195000
9
10			SOV
11	961214-00		35000
12			Design LOI
13
14	961215-00		835270
15
16			SOV
17	961216-00		342400
18
19			SOV
20	961217-00			180400
21
22				SOV
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"/>

and here is an example of what the sales history sheet looks like. Formulas are listed off to the side:

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
24		A	B	C	D	E		Formulas
25	1	project #:						fig 1.	=IFNA(INDEX(NSRDATA,MATCH($B$1,SREFROWS,0),MATCH(A3,SREFCOLS,0)),"")
26	2	MONTH	AMOUNT	SALE TYPE	DESCRIPTION	Filter		fig 2.	=IFNA(OFFSET(INDEX(NSRDATA,MATCH(B$1,SREFROWS,0),MATCH(A3,SREFCOLS,0)),1,0),"")
27	3	Mar-16	[fig 1.]	[fig 2.]	[fig 3.]	[fig 4.]		fig 3.	=IFNA(OFFSET(INDEX(NSRDATA,MATCH(B$1,SREFROWS,0),MATCH(A3,SREFCOLS,0)),2,0),"")
28	4	Apr-16						fig 4.	=IF(ISNUMBER(B3),1,"")
29	5	May-16
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">