=IF(P28=0,"",IF(iserror(INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d
Lines'!$A$2:$A$230,0),0),1)+1))),"",INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)))))

"Eva Marie" wrote:

Hi Shane,

I'm sorry, but I don't understand your question. The formula is given
below. The ooriginal formula was:

=IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1))

With the original formula, The MATCH and INDEX are looking up dates in
another tab and returning either a date (e.g., 10/13/08), or returning #N/A
(and displaying the #N/A).

The revised formula is the one provided by Max:

= IF(OR(P28=0,A28=0), IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)),
IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d
Lines'!$A$2:$A$230,0),0),1)),"",INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"")

This formula is still looking up the date and if there isn't one, it hides
the #N/A now, but if there IS a date, it ALSO hides the date.

Can you please tell me what else I need to provide?

Thank you,
Eva Marie

"ShaneDevenshire" wrote:

Hi,

Please show us your formula. We are working in the dark here.

There is nothing wrong with any of the formulas suggested by Niek or Max.
The problem is they don't know your formula and data.
--
Thanks,
Shane Devenshire


"Eva Marie" wrote:

Hi Max,

Thank you for your help too! I copied this entire formula into the cell and
then dragged it down into the below cells to copy the formula into them.
Unfortunately, when there is a value to be displayed, such as "10/13/08", it
makes the cell blank, rather than displaying 10/13/08. It definitely works
with the #N/A cells, where no value was returned.

I'd appreciate any help on getting values to display, while still hiding the
#N/A.

Thanks!
Eva Marie

"Max" wrote:

The key is to use ISNA like this: =IF(ISNA(MATCH(...)),"",<formula)
Indicatively for your expression, which uses 2 MATCHes:
IF(OR(ISNA(MATCH(1)),ISNA(MATCH(2))),"",INDEX(..,M ATCH(1),MATCH(2))..)

Try (all in the same cell):
=
IF(OR(P28=0,A28=0),
IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)),
IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d
Lines'!$A$2:$A$230,0),0),1)),"",
INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:58
xdemechanik
---
"Eva Marie" wrote:
I have a formula which is working correctly; however when it doesn't locate
the data (as will happens sometimes), it displays the #N/A error. I would
prefer it display a blank cell. Can someone please tell me how to do this in
Excel?

Formula: =IF(P28=0,"",INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1))

Thanks,
Eva Marie