lookup
 

I think this has an easy fix, but I can't seem to get my head around it right
now.

I have a column of data which has a variety of formulas in it that return a
variety of words. I would like a way to know if two certain words are in that
column. If they are, I want to know which word is present. Here is an example

Bob
Mike
Tom
Alan

I want to know if it says "John" or if it says "Tom" and it to return which
name so my final result here would be "Tom". My data will never have both a
John and a Tom in the same column, but Tom or John could be in row 3 or 4 or
5, etc.

lookup
 

I think this'll work

=IF(COUNTA(G1:G4)=COUNTA(SUBSTITUTE(G1:G4,"Bob","" )),"john","bob")

"jenhow" wrote:

I think this has an easy fix, but I can't seem to get my head around it right
now.

I have a column of data which has a variety of formulas in it that return a
variety of words. I would like a way to know if two certain words are in that
column. If they are, I want to know which word is present. Here is an example

Bob
Mike
Tom
Alan

I want to know if it says "John" or if it says "Tom" and it to return which
name so my final result here would be "Tom". My data will never have both a
John and a Tom in the same column, but Tom or John could be in row 3 or 4 or
5, etc.

lookup
 

No - that doesn't work - should have tested it first

Try

=IF(SUMPRODUCT(--(G1:G4="Bob"))0,"Bob","John")

"jenhow" wrote:

I think this has an easy fix, but I can't seem to get my head around it right
now.

I have a column of data which has a variety of formulas in it that return a
variety of words. I would like a way to know if two certain words are in that
column. If they are, I want to know which word is present. Here is an example

Bob
Mike
Tom
Alan

I want to know if it says "John" or if it says "Tom" and it to return which
name so my final result here would be "Tom". My data will never have both a
John and a Tom in the same column, but Tom or John could be in row 3 or 4 or
5, etc.

lookup
 

=IF(ISERROR(VLOOKUP("Tom",A1:A20,1,0)),"John","Tom ")
or
=IF(COUNTIF(A1:A20,"John"),"John","Tom")
or
=IF(A1:A20="John","John","Tom")


"jenhow" wrote:

I think this has an easy fix, but I can't seem to get my head around it right
now.

I have a column of data which has a variety of formulas in it that return a
variety of words. I would like a way to know if two certain words are in that
column. If they are, I want to know which word is present. Here is an example

Bob
Mike
Tom
Alan

I want to know if it says "John" or if it says "Tom" and it to return which
name so my final result here would be "Tom". My data will never have both a
John and a Tom in the same column, but Tom or John could be in row 3 or 4 or
5, etc.

lookup
 

=IF(A1:A20="John","John","Tom")

Do you test the formulas that you post?

Biff

"Teethless mama" wrote in message
...
=IF(ISERROR(VLOOKUP("Tom",A1:A20,1,0)),"John","Tom ")
or
=IF(COUNTIF(A1:A20,"John"),"John","Tom")
or
=IF(A1:A20="John","John","Tom")


"jenhow" wrote:

I think this has an easy fix, but I can't seem to get my head around it
right
now.

I have a column of data which has a variety of formulas in it that return
a
variety of words. I would like a way to know if two certain words are in
that
column. If they are, I want to know which word is present. Here is an
example

Bob
Mike
Tom
Alan

I want to know if it says "John" or if it says "Tom" and it to return
which
name so my final result here would be "Tom". My data will never have
both a
John and a Tom in the same column, but Tom or John could be in row 3 or 4
or
5, etc.

lookup
 

Harlan?

Is that you????

<gd&r

"T. Valko" wrote:

=IF(A1:A20="John","John","Tom")

Do you test the formulas that you post?

Biff

"Teethless mama" wrote in message
...
=IF(ISERROR(VLOOKUP("Tom",A1:A20,1,0)),"John","Tom ")
or
=IF(COUNTIF(A1:A20,"John"),"John","Tom")
or
=IF(A1:A20="John","John","Tom")


"jenhow" wrote:

I think this has an easy fix, but I can't seem to get my head around it
right
now.

I have a column of data which has a variety of formulas in it that return
a
variety of words. I would like a way to know if two certain words are in
that
column. If they are, I want to know which word is present. Here is an
example

Bob
Mike
Tom
Alan

I want to know if it says "John" or if it says "Tom" and it to return
which
name so my final result here would be "Tom". My data will never have
both a
John and a Tom in the same column, but Tom or John could be in row 3 or 4
or
5, etc.



--

Dave Peterson

lookup
 

<gd&r

I had to Google that one!

Biff

"Dave Peterson" wrote in message
...
Harlan?

Is that you????

<gd&r

"T. Valko" wrote:

=IF(A1:A20="John","John","Tom")

Do you test the formulas that you post?

Biff

"Teethless mama" wrote in
message
...
=IF(ISERROR(VLOOKUP("Tom",A1:A20,1,0)),"John","Tom ")
or
=IF(COUNTIF(A1:A20,"John"),"John","Tom")
or
=IF(A1:A20="John","John","Tom")


"jenhow" wrote:

I think this has an easy fix, but I can't seem to get my head around
it
right
now.

I have a column of data which has a variety of formulas in it that
return
a
variety of words. I would like a way to know if two certain words are
in
that
column. If they are, I want to know which word is present. Here is an
example

Bob
Mike
Tom
Alan

I want to know if it says "John" or if it says "Tom" and it to return
which
name so my final result here would be "Tom". My data will never have
both a
John and a Tom in the same column, but Tom or John could be in row 3
or 4
or
5, etc.



--

Dave Peterson

lookup
 

That works great once the data is filled in, but can I make it so that if no
data is filled it, it won't give me "John" as an answer? So I would like
nothing returned if neither john or bob is present, then once the column has
data it will find john or bob. Does that make sense? Thanks for you help.

"Duke Carey" wrote:

No - that doesn't work - should have tested it first

Try

=IF(SUMPRODUCT(--(G1:G4="Bob"))0,"Bob","John")

"jenhow" wrote:

I think this has an easy fix, but I can't seem to get my head around it right
now.

I have a column of data which has a variety of formulas in it that return a
variety of words. I would like a way to know if two certain words are in that
column. If they are, I want to know which word is present. Here is an example

Bob
Mike
Tom
Alan

I want to know if it says "John" or if it says "Tom" and it to return which
name so my final result here would be "Tom". My data will never have both a
John and a Tom in the same column, but Tom or John could be in row 3 or 4 or
5, etc.