Match Formula Comes up Blank

[Jay06]

Hi -

I've tried using a match formula along with an =IF(ISERROR(MATCH( formula, but come up blank although I know that there are matches. I've tried changing the format of the cells for both sheets not sure what else could be causing it. I have the formula in column G for the first tab and column I for the second tab.

Thanks!
J

 

[Deepak]

Hi,

Your's match function doesn't produced the output due to yours data format.

In Sheet1 Col D is a numeric value where Col G in sheet2 is a text col.

So, you are matching a numeric value to text.

Check this..

=IF(SUMPRODUCT(--(--('2'!$G$1:$G$268)=D2)),"",D2)

 

[Jay06]

Thank you!! Works!

Is there anyway to format so that these columns are both numbers? I tried formatting but it doesn't seem to stay.

 

[barry houdini]

COUNTIF could be used here as it doesn't distinguish between numbers formatted as text or those formatted as numbers, i.e.

=IF(COUNTIF('2'!$G$1:$G$268,D2),"",D2)

.....but if you want to change the format to numeric try this:

Select column to convert > Data > Text to columns > Finish

Now you will have numbers rather than text

regards, barry

 

[Jay06]

Thanks so much!