CODEWITHSUNDEEP
excelexcel-formula
Brian Crichton
Brian Crichton

Reputation: 5

Look up/search one of three criteria and return appropriate value

I am looking for the correct formula for a table I am working on. What I would like is:

0 for say B1 if A1 equals "None",
1 if A1 equals "In" and
2 if A1 equals "out".

Upvotes: 0

Views: 63

Answers (5)

user4039065
user4039065

Reputation:

Maybe just one more.

=(A1="in")+(A1="out")*2

Upvotes: 1

pnuts
pnuts

Reputation: 59475

A little shorter: 

=(A1<>"None")*(A1="In")+2*(A1="Out")

Upvotes: 1

Excel Hero
Excel Hero

Reputation: 14764

Try this:

=CHOOSE(1+SEARCH(A1,"noneinout"),0,,,,,1,,2)

Upvotes: 3

Bon
Bon

Reputation: 3103

Use nested if formula

=IF(A1="None", 0, IF(A1="In", 1, IF(A1="out", 2, -1) ))

Upvotes: 0

rotgers
rotgers

Reputation: 2020

The formula will look something like this: =IF(G18="None",0,IF(G18="ln",1,IF(G18="out",2,0)))

Just replace G18 with the correct cell address that contains the None|ln|out value.

Upvotes: 0

Related Questions