#N/A

[Steved]

Hello from Steved

The below formula gives me #N/A if there is no value in
the cell. Could you please tell how I can make the cell
blank, until a value is added.

=INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit
Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'!
$A$1:$C$1,))

Thankyou.

 

[Norman Harker]

Hi Steved!

=IF(ISNA(INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit
Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'!
$A$1:$C$1,)),"",INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit
Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'!
$A$1:$C$1,)))

IOW you use a standard construct:

=IF(ISNA(YourFormula),"",YourFormula)

 

[RagDyer]

You had a few redundant commas in there, so see how this works:

=IF(ISNA(MATCH(E2:E2001,'Audit Team'!$A$1:$A$2000)),"",INDEX('Audit
Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit Team'!$A$1:$A$2000),
MATCH("Surname",'Audit Team'!$A$1:$C$1)))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hello from Steved

The below formula gives me #N/A if there is no value in
the cell. Could you please tell how I can make the cell
blank, until a value is added.

=INDEX('Audit Team'!$A$1:$C$2000, MATCH(E2:E2001,'Audit
Team'!$A$1:$A$2000,), MATCH("Surname",'Audit Team'!
$A$1:$C$1,))

Thankyou.

 

[Steved]

Hello Norman from Steved

I'm getting an error.

It is highlighting ""

Can you assist me as to what my problem is please.

Thankyou.

 

[Norman Harker]

Hi Steved!

Try deleting the "" and re-entering them. I find that if I copy and
paste formulas there are occasions when the "" are interpreted wrong /
differently.

 

[Steved]

Thankyou Norman

-----Original Message-----
Hi Steved!

Try deleting the "" and re-entering them. I find that if I copy and
paste formulas there are occasions when the "" are interpreted wrong /
differently.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)



.

 

[Steved]

Thankyou RD