if & isnumber (& vlookup)

[kelvin_9]

i am trying to write a formula to return my value with request:
#1 ) if cell B2 value is "full", return with "anytime" in C2
#2 ) if cell B3 value is "0", return with ""(leave it blank) in C3
#3 ) if cell B4 value is contain "0:00", return with the original value in C4

actually i want to use if+vlookup, or any better idea?
in #3 , ":00" is a time value in 00:00-00:00 with format general, but i can't return successfully if i use search(timevalue(hh:mm-hh:mm) function

=IF(ISNUMBER(SEARCH("full",availability!E9)),"anytime","")&IF(ISNUMBER(SEARCH("0",availability!E9)),"","")&IF(ISNUMBER(SEARCH(":00",availability!E9)),availability!E9,"")

[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]request[/TD]
[TD]return[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]full[/TD]
[TD]anytime[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]0[/TD]
[TD]leave it blank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]08:00-20:00[/TD]
[TD]original value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


thanks

 

[kelvin_9]

thanks MB110, i will try later after work.
to tackle any change of my team's availability, can i simply add ":15/:30 even :45" in to your formula?
=IF(B4="full","anytime",IF(B4=0,"",IF(OR(MID(B4,3,3)=":00",MID(B4,9,3)=":00"),MID(B4,3,3)=":15",MID(B4,9,3)=":15"),MID(B4,3,3)=":30",MID(B4,9,3)=":30"),MID(B4,3,3)=":45",MID(B4,9,3)=":45"),B4,""))) ?

if so, how about vlookup to make sure no change will be made if sequence in column A changed?
eg. this time A2-Apple, A3-Peter, A4-John / next time A2-Peter, A3-John, A4-Apple... and so on.

thanks again MB110

hello, any possible?

 

[MB110]

Sorry, I cant write the formula to handle all of the possibilities. Maybe someone else. You could use vlookup if you put all of the possibilities into a table but you will still need the mid function to extract from the string. The data input design is flawed, making it more difficult to get the results you desire. That is, if I even understand what you're trying to do. I'm not really confident that I'm following you. I'm sorry. Hopefully someone more advanced can assist you. I'm only an intermediate user of Excel.