Formula Question - Index Match Blanks

[rjacmuto32]

I have this formula below to return me sevearl rows of data from a specs tab based on what ID is in a cell (C7, D7, etc for each column). It works great except for 1 thing. If the field I am returning on the Specs tab is blank, this formula puts a 0 instead of returning the blank. Is there anyway to return a Blank and not a 0 is the cooresponding line on the spec tab has a blank cell value.


=INDEX('Specs'!$B$2:$CW$69,ROW($A1),MATCH(C$7,'Specs'!$B$2:$CW$2,0))

 

[3G]

You could always embed the index formula into an "IF" statement..something like IF(NamedRange>1,INDEX('Specs'!$B$2:$CW$69,ROW($A1),MATCH(C$7,'Specs'!$B$2:$CW$2,0)),""),

where NamedRange is $B$2:$CW$69.

 

[rjacmuto32]

would the IF statement slow the calculation down at all if I have about 3,000 formula's on the tab?

 

[3G]

That, I cannot answer sorry.

 

[bobhc]

Good day rjacmuto32


You have the work book with all the data. Could you not do a save as with new name put the if statement in and run and see if there is any speed change and let the forum know

 

[Debraj]

Hi rjacmuto32..


You have set your excel to display "blank" as "0", thats why its showing all "blanks" as "0"..


What.. you have not set you Excel.. !! then why ..

Excel > Option > Advance > Display Option for this Worksheet > "Show Zero in Cells that have ZERO value" is checked in your PC..


BTW.. If you want to display BLANK as BLANK then you can use the below formula..

=IF(LEN( INDEX('Specs'!$B$2:$CW$69,ROW($A1),MATCH(C$7,'Specs'!$B$2:$CW$2,0))) = 0,"",INDEX('Specs'!$B$2:$CW$69,ROW($A1),MATCH(C$7,'Specs'!$B$2:$CW$2,0)) )

Regards,

Deb