If isnumber match to ignore blank cells

[Toonies]

Hi and thanks for looking at my question, which is

The following formula works ok with one exception.

=IF(ISNUMBER(MATCH('Personnel Rota'!C10,Task)),"","Working")

when 'Personnel Rota'!C10 is blank the answer cell at present it displays "Working"

What I need is for the formula to ignore blank cells.

Is this possible.

Many thanks

Toonies

 

[JackDanIce]

Try:

=IF(OR(ISBLANK('Personnel Rota'!C10),ISNUMBER(MATCH('Personnel Rota'!C10,Task))),"","Working")

 

[Toonies]

Thats perfect!

WOW was that quick

Many thanks

Toonies

 

[Peter_SSs]

Without knowing the sorts of things that might be in 'Personnel Rota'!C10 or in 'Task', or just what you are trying to achieve, my guess is that you may need something like this

=IF('Personnel Rota'!C10="","",IF(ISNUMBER(MATCH('Personnel Rota'!C10,Task,0)),"","Working"))

 

[haddonsman]

Out of curiosity, is there any advantage / disadvantage to using a VLOOKUP in this instance, such as:

=IF('Personnel Rota'!C10="","",IF(ISERROR(VLOOKUP('Personnel Rota' C10,task,1,0)),"Working",""))

I've inherited a ton of workbooks that use that technique rather than ISNUMBER and Match and am now wondering what's the most efficient method.

 

[Toonies]

Hi many thanks to you all.

Each solution works Amazing, just shows how diverse Excel is and I take my hat of to you all.

Cheers

Toonies

 

[Peter_SSs]

haddonsman

I'm not an expert in formula efficiencies but my feeling is that in checking in a single column range as I believe is happening in this example there shouldn't be much difference between
ISERROR(VLOOKUP(... and
ISNUMBER(MATCH(...

If I'm wrong I'm sure somebody will correct me soon enough.

 

[Aladin Akyurek]

haddonsman

I'm not an expert in formula efficiencies but my feeling is that in checking in a single column range as I believe is happening in this example there shouldn't be much difference between
ISERROR(VLOOKUP(... and
ISNUMBER(MATCH(...

If I'm wrong I'm sure somebody will correct me soon enough.

IsNumber/Match (with match-type 0) shows a better temporal profile compared to Vlookup with large data sets.

 

[haddonsman]

Thanks for that - I'm trying to move away from VLOOKUPs so this is good to hear!