Add if blank statement to index match

[Diddy]

Hi everyone,

I'm using the following formula to get data from a table

=INDEX(EMP2009,MATCH($N$1,BuildingNo,0))

If there is no data matching the building number and employee data in the
appropriate row of EMP2009, then I'd like the formula to return a blank.

I'm getting all tied up trying to add an if statement and it would be
fantastic if anyone could help.

Cheers
Diddy

 

[Jacob Skaria]

Use ISNA() and IF()

=IF(ISNA(MATCH($N$1,BuildingNo,0)),"",INDEX(EMP2009,
MATCH($N$1,BuildingNo,0)))

If this post helps click Yes

 

[Jarek Kujawa]

would:

=IF(ISERROR(INDEX(EMP2009,MATCH($N$1,BuildingNo,0))),"",INDEX
(EMP2009,MATCH($N$1,BuildingNo,0)))

help?

 

[Diddy]

Hi Jacob,

Thanks for replying

I'm still getting a zero.

Does it make any differnce to know that, BuildingNo will always have a
number but Emp2009 sometimes has a value but sometimes is empty?

This confirms that I haven't really got INDEX and MATCH

Cheers
Diddy

 

[Jacob Skaria]

Try the below

=IF(ISNA(MATCH($N$1,BuildingNo,0)),"",
IF(INDEX(EMP2009,MATCH($N$1,BuildingNo,0))="","",
INDEX(EMP2009,MATCH($N$1,BuildingNo,0))))

If this post helps click Yes

 

[T. Valko]

If the value being retunred is TEXT you can reduce that to:

=IF(ISNA(MATCH($N$1,BuildingNo,0)),"",
T(INDEX(EMP2009,MATCH($N$1,BuildingNo,0))))

 

[Diddy]

Hi Jacob,

Just the job!

Thank you so much!!

Cheers
Diddy