Lookup several names and put a Code if found I need a formula that will look for

Question

Lookup several names and put a Code if found

I need a formula that will look for certain names. See the example below.

In this example it would look for: "Joe", "Bob", "Jon", and "Jay" and put in "Code 1". For ALL other names it should put in "Code 2".

Can that be done with 1 formula in the Result column? I will have 9 separate names to find for Code 1 and about 8 names that end up with Code 2 because they won't be listed in the formula.

I tried this formula but it doesn't work with more than 2 names listed. I get the error "#VALUE!" for the below.

=IF(OR(A2="Joe",A2="Bob",A2="Jon",A2="Jay"),"Code 1","Code 2")

Thanks in advance for any help!

Explanation / Answer

Your formula works just fine for me but I prefer this one.

Drag down as required.

=IF(A2<>"",IF(ISNUMBER(MATCH(A2,{"Joe","Bob","Jon","Jay"},0)),"Code 1","Code 2"),"")

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.