Matching text in a cell range

[StPeach]

Hi,

I'm having timetabling issues. I have a spreadsheet that lists the staff scheduled to teach lessons in one column and a list of support staff in the neighbouring column. At the bottom of one of these columns I want to make a list of staff who are not currently allocated in either of the columns so management can easily see who else is available.

I can get Excel to return a blank if it sees staff initials listed in the column with the following formula and then use conditional formatting to highlight non-blanks. This formula works well to return a blank if staff have been allocated in a single column...

=IF(ISNUMBER(MATCH("DC",D4:D33,0)),"","DC")

The problem is, as soon as I specify a range of cells (2 columns) like this...

=IF(ISNUMBER(MATCH("DC",D4:E33,0)),"","DC")

it returns the staff code instead of a blank.

I tried posting some screen shots yesterday but it uploaded the code rather than the image, so I hope someone gets this without it!

Thanks in advance - this is starting to bug me!

 

[steve the fish]

MATCH doesn't work with multiple columns. Try something like:

=IF(COUNTIF(A23:B31,"*DC*"),"DC","")

 

[StPeach]

Fabulous! Thank you very much!