#N/A invisible

[asyamonique]

{=INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0))}

How can i make invisible the #N/A text from empty cells on that code given above?
Thanks

 

[Jonmo1]

Try

=IF(ISNA(MATCH(C32,C$5:C$24,0)),"",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))


Side note, this is not an array formula (doesn't need the {}), does not need to be entered with CTRL + SHIFT + ENTER

 

[AlphaFrog]

Try somethig like this...

=IF(ISNA(MATCH(C32,C$5:C$24,0)), "", INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))

 

[asyamonique]

Thanks Guys

 

[asyamonique]

Try somethig like this...

=IF(ISNA(MATCH(C32,C$5:C$24,0)), "", INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))

Sheet1.Cells(32, 2).Resize(9, 1).Formula = "=INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0))"

How can alter that code? Coz when i put ur formula it is giving error.
When i use it on cell its ok but with that below code none!!
Cheers

Sheet1.Cells(32, 2).Resize(9, 1).Formula = "=IF(ISNA(MATCH(C32,C$5:C$24,0)),"",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))"

 

[AlphaFrog]

The problem is the quotes within the text string of the formula.

Sheet1.Cells(32, 2).Resize(9, 1).Formula = 
"=IF(ISNA(MATCH(C32,C$5:C$24,0)),[COLOR="Red"]""[/COLOR],INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))"

To express two literal quotes within a VBA text string, use two quotes for each or in this case four quotes total.

Sheet1.Cells(32, 2).Resize(9, 1).Formula = 
"=IF(ISNA(MATCH(C32,C$5:C$24,0)),[COLOR="Red"]""""[/COLOR],INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0)))"

Also, you'll want to express the formula in R1C1 notation so the cell references are correct for each cell in the Range, or put the formula in the 1st cell and use .Autofill to fill it in the other cells.