lookup

[Guest]

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.

 

[Guest]

I think this'll work

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

 

[Guest]

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

Try

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

 

[Guest]

=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")

 

[T. Valko]

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

Do you test the formulas that you post?

Biff

 

[Dave Peterson]

Harlan?

Is that you????

<gd&r>

Do you test the formulas that you post?

Biff

 

[T. Valko]

<gd&r>

I had to Google that one!

Biff

Harlan?

Is that you????

<gd&r>

 

[Guest]

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.