Add if blank statement to index match
 

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

Add if blank statement to index match
 

Use ISNA() and IF()

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

If this post helps click Yes
---------------
Jacob Skaria


"Diddy" wrote:

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

Add if blank statement to index match
 

would:

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

help?


On 3 Lis, 12:04, Diddy wrote:
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

Add if blank statement to index match
 

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" wrote:

Use ISNA() and IF()

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

If this post helps click Yes
---------------
Jacob Skaria


"Diddy" wrote:

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

Add if blank statement to index match
 

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
---------------
Jacob Skaria


"Diddy" wrote:

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" wrote:

Use ISNA() and IF()

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

If this post helps click Yes
---------------
Jacob Skaria


"Diddy" wrote:

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

Add if blank statement to index match
 

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))))


--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"Diddy" wrote:

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" wrote:

Use ISNA() and IF()

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

If this post helps click Yes
---------------
Jacob Skaria


"Diddy" wrote:

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

Add if blank statement to index match
 

Hi Jacob,

Just the job! :-)

Thank you so much!!

Cheers
Diddy

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Diddy" wrote:

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" wrote:

Use ISNA() and IF()

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

If this post helps click Yes
---------------
Jacob Skaria


"Diddy" wrote:

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