Need help with Excel 2000 text-related functions
 

How do I search a range of text cells and return value(s) based on my findings?

From C18, I want to search C3:C14, said cells sometimes containing names, to
see if one particular name is there. If it is, I want to leave C18 blank.
If it isn't, I want to insert the name in C18.

I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!".

Help?

Need help with Excel 2000 text-related functions
 

Assuming A1 contains the name you are looking for put this formula in C18

=IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"")

Tyro

"Joan A" <Joan wrote in message
...
How do I search a range of text cells and return value(s) based on my
findings?

From C18, I want to search C3:C14, said cells sometimes containing names,
to
see if one particular name is there. If it is, I want to leave C18 blank.
If it isn't, I want to insert the name in C18.

I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!".

Help?

Need help with Excel 2000 text-related functions
 

Hi Joan,

Suppose you are looking for the name Shane in the range C3:C14. The formula
in cell C18 would read:

=IF(OR(C3:C14="Shane"),"Shane","")

This is an array formula so when enter it you hold down Ctrl+Shift+Enter.
To shorten this you can put the name you are searching for in another cell,
say A1, then the formula becomes

{=IF(OR(G2:G7=A1),A1,"")}

The outer {}'s are not typed but you will see them in the cell when you
press Ctrl+Shift+Enter.

Cheers,
Shane Devenshire

"Joan A" <Joan wrote in message
...
How do I search a range of text cells and return value(s) based on my
findings?

From C18, I want to search C3:C14, said cells sometimes containing names,
to
see if one particular name is there. If it is, I want to leave C18 blank.
If it isn't, I want to insert the name in C18.

I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!".

Help?

Need help with Excel 2000 text-related functions
 

Thanks, this worked!

But now, I'm wondering if I can add another IF in the same cell. I only
want the first formula to run if the value in another cell equals something.
For instance, if B16<OFF, then run =IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"").
Or if it does equal OFF, then a blank cell overrides the formula you provided.

Clear as mud?


"Tyro" wrote:

Assuming A1 contains the name you are looking for put this formula in C18

=IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"")

Tyro

"Joan A" <Joan wrote in message
...
How do I search a range of text cells and return value(s) based on my
findings?

From C18, I want to search C3:C14, said cells sometimes containing names,
to
see if one particular name is there. If it is, I want to leave C18 blank.
If it isn't, I want to insert the name in C18.

I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!".

Help?

Need help with Excel 2000 text-related functions
 

Thanks for your reply, but this didn't work. Blank when there should have
been something, error message when there shouldn't have been anything.



"Shane Devenshire" wrote:

Hi Joan,

Suppose you are looking for the name Shane in the range C3:C14. The formula
in cell C18 would read:

=IF(OR(C3:C14="Shane"),"Shane","")

This is an array formula so when enter it you hold down Ctrl+Shift+Enter.
To shorten this you can put the name you are searching for in another cell,
say A1, then the formula becomes

{=IF(OR(G2:G7=A1),A1,"")}

The outer {}'s are not typed but you will see them in the cell when you
press Ctrl+Shift+Enter.

Cheers,
Shane Devenshire

"Joan A" <Joan wrote in message
...
How do I search a range of text cells and return value(s) based on my
findings?

From C18, I want to search C3:C14, said cells sometimes containing names,
to
see if one particular name is there. If it is, I want to leave C18 blank.
If it isn't, I want to insert the name in C18.

I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!".

Help?

Need help with Excel 2000 text-related functions
 

=IF(B16<"OFF",IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1, ""),"")

Tyro


"Joan A" wrote in message
...
Thanks, this worked!

But now, I'm wondering if I can add another IF in the same cell. I only
want the first formula to run if the value in another cell equals
something.
For instance, if B16<OFF, then run
=IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"").
Or if it does equal OFF, then a blank cell overrides the formula you
provided.

Clear as mud?


"Tyro" wrote:

Assuming A1 contains the name you are looking for put this formula in C18

=IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"")

Tyro

"Joan A" <Joan wrote in message
...
How do I search a range of text cells and return value(s) based on my
findings?

From C18, I want to search C3:C14, said cells sometimes containing
names,
to
see if one particular name is there. If it is, I want to leave C18
blank.
If it isn't, I want to insert the name in C18.

I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!".

Help?

Need help with Excel 2000 text-related functions
 

=IF(B16="OFF","",IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A 1,""))
--
David Biddulph

"Joan A" wrote in message
...
Thanks, this worked!

But now, I'm wondering if I can add another IF in the same cell. I only
want the first formula to run if the value in another cell equals
something.
For instance, if B16<OFF, then run
=IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"").
Or if it does equal OFF, then a blank cell overrides the formula you
provided.

Clear as mud?


"Tyro" wrote:

Assuming A1 contains the name you are looking for put this formula in C18

=IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"")

Tyro

"Joan A" <Joan wrote in message
...
How do I search a range of text cells and return value(s) based on my
findings?

From C18, I want to search C3:C14, said cells sometimes containing
names,
to
see if one particular name is there. If it is, I want to leave C18
blank.
If it isn't, I want to insert the name in C18.

I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!".

Help?

Need help with Excel 2000 text-related functions
 

Another one:

=IF(B16="OFF","",IF(COUNTIF(C3:C14,A1),"",A1))

--
Biff
Microsoft Excel MVP


"Joan A" wrote in message
...
Thanks, this worked!

But now, I'm wondering if I can add another IF in the same cell. I only
want the first formula to run if the value in another cell equals
something.
For instance, if B16<OFF, then run
=IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"").
Or if it does equal OFF, then a blank cell overrides the formula you
provided.

Clear as mud?


"Tyro" wrote:

Assuming A1 contains the name you are looking for put this formula in C18

=IF(ISNA(VLOOKUP(A1,C3:C14,1,0)),A1,"")

Tyro

"Joan A" <Joan wrote in message
...
How do I search a range of text cells and return value(s) based on my
findings?

From C18, I want to search C3:C14, said cells sometimes containing
names,
to
see if one particular name is there. If it is, I want to leave C18
blank.
If it isn't, I want to insert the name in C18.

I've tried IF multiple ways and it either returns "FALSE" or "#VALUE!".

Help?