Need formula

[travelinman]

I am looking for a way to have Cell C1 look at A1 and determine by the text
to add 1 or 2 to the date in B1. A1 may contain up to 10 Statements. Four of
the statements will require adding 1 to B1 and 6 of the statements will
require adding 2 to B1. Can this be done and if yes I need help. Thanks.


Cell A1 Cell B1
Cell C1
<Report Request> 18-Aug <B1 +1>

 

[Lars-Åke Aspelin]

I am looking for a way to have Cell C1 look at A1 and determine by the text
to add 1 or 2 to the date in B1. A1 may contain up to 10 Statements. Four of
the statements will require adding 1 to B1 and 6 of the statements will
require adding 2 to B1. Can this be done and if yes I need help. Thanks.


Cell A1 Cell B1
Cell C1
<Report Request> 18-Aug <B1 +1>

Try this formula in cell C1:

=B1+LOOKUP(A1,{0,"Statement 1","Statement 2","Statement 3"},{0,1,2,1})

In this example there are only three statements, you can easily add
more statments.
The order of the statements is not important, just make sure that the
order of the numbers in the result vector corresponds to the
statements.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Try this formula in cell C1:

=B1+LOOKUP(A1,{0,"Statement 1","Statement 2","Statement 3"},{0,1,2,1})

In this example there are only three statements, you can easily add
more statments.
The order of the statements is not important, just make sure that the
order of the numbers in the result vector corresponds to the
statements.

Hope this helps / Lars-Åke

You did not mention what the result in C1 should be if A1 is empty (if
that is at all possible).
The proposed formula adds 0 to the date in B1 if A1 is empty.

Here is an alternative displaying the warning text A1 is empty" if A1
is empty.

=IF(A1="","A1 is empty",B1+LOOKUP(A1,{"Statement 1","Statement
2","Statement 3"},{1,2,1}))

And here is another one with even more input (A1) checking

=IF(ISERROR(LOOKUP(A1,{"Statement 1","Statement 2","Statement
3"})),"Invalid value in A1",B1+LOOKUP(A1,{"Statement 1","Statement
2","Statement 3"},{1,2,1}))

Hope this helps / Lars-Åke

 

[T. Valko]

Create a 2 column table that lists the text and its corresponding value.
Like this:

...........E..........F
1.....text1.......1
2.....text2.......1
3.....text3.......2
4.....text4.......1

Then use this formula:

=B1+VLOOKUP(A1,E1:F4,2,0)