I have the following formula:

{=INDEX(nursetb!$D$1:$D$1354,MATCH(1,(ytd!C$2=nurs etb!$A$1:$A$1354)*(ytd!
$A6=nursetb!$B$1:$B$1354),0))}


Which works great, but if there is no information to match etc it returns a
#n/a any chance I can get this to return a blank somehow.

Many thanks in advance

Petes girl

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

I'd try:

=if(sumproduct((ytd!C$2=nursetb!$A$1:$A$1354)*(ytd !$A6=nursetb!$B$1:$B$1354))=0,
"",yourindexformulahere)))



"petes_girl via OfficeKB.com" wrote:

I have the following formula:

{=INDEX(nursetb!$D$1:$D$1354,MATCH(1,(ytd!C$2=nurs etb!$A$1:$A$1354)*(ytd!
$A6=nursetb!$B$1:$B$1354),0))}


Which works great, but if there is no information to match etc it returns a
#n/a any chance I can get this to return a blank somehow.

Many thanks in advance

Petes girl

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

--

Dave Peterson

Hi Dave

thanks for that, will I have to enter the whole formula as an array ie using
ctrl shift and enter?



Dave Peterson wrote:
I'd try:

=if(sumproduct((ytd!C$2=nursetb!$A$1:$A$1354)*(yt d!$A6=nursetb!$B$1:$B$1354))=0,
"",yourindexformulahere)))

I have the following formula:

[quoted text clipped - 11 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

Yes, because the MATCH portion is still an array.

Biff

"petes_girl via OfficeKB.com" <u28569@uwe wrote in message
news:6d1f52e3a1108@uwe...
Hi Dave

thanks for that, will I have to enter the whole formula as an array ie
using
ctrl shift and enter?



Dave Peterson wrote:
I'd try:

=if(sumproduct((ytd!C$2=nursetb!$A$1:$A$1354)*(y td!$A6=nursetb!$B$1:$B$1354))=0,
"",yourindexformulahere)))

I have the following formula:

[quoted text clipped - 11 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1