Conditional Formatting with Index/Match function

Hello,

I'm struggling with adding conditional formatting to a function that combines index and match

Here is the function:
=INDEX('Sheet2'!$C$7:$C$91,MATCH(A12,'Sheet2'!$B$7:$B$91,0))

So this function returns values when there is a value, but more often than not it returns #N/A when there's no match.

How do I white-out the #N/A to unclutter how my page looks? I tried to use the "text that contains" option on conditional formatting but nothing happens. Do I need to add some sort of If function (maybe with istext) around the existing function?

Thank you for the help.

You could modify your original formula to prevent the #N/A like so (in Excel 2007)

=IFERROR(INDEX('Sheet2'!$C$7:$C$91,MATCH(A12,'Sheet2'!$B$7:$B$91,0)),"")

or, if you want to white out the #N/A
Assuming column A beginning with A2
=ISNA(A2)
Does that work for you?

Wrap your formula in this IF() function
=IF(ISNUMBER(MATCH(A12,Sheet2!$B$7:$B$91,0)),"your formula","")

i.e.

Please Login or Register  to view this content.

Hope this helps

Kindly help in attachment file to remove #NUM!

tanks to all