Combination Formula Assistance needed

[JamesDLaurie]

Good Afternoon,

I need some assistance from the group, please... I need to generate a formula that will concatenate the left two characters in cell A2 with the first three characters in B2. Then take those results and compare them to a predetermined group of values and return a text response for a TRUE value or a different text response for a FALSE. I can handle the standard IF/THEN statement, where I'm struggling is having it compare to the list that I need to enter separately into the formula. See below:

[TABLE="width: 134"]
<tbody>[TR]
[TD]Project [/TD]
[TD] Activity[/TD]
[/TR]
[TR]
[TD]ABCDEFG[/TD]
[TD="align: right"] 123456789[/TD]
[/TR]
[TR]
[TD]DEFGHIJ[/TD]
[TD="align: right"]987654321[/TD]
[/TR]
[TR]
[TD]LQYSXP[/TD]
[TD="align: right"]784474215[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[Jonmo1]

Welcome to the board.

You need to give more details, what is the predetermined group of values? And what makes the comparison True or False ?

But you can start with
The concatenation is easy enough
=LEFT(A2,2)&LEFT(B2,3)

But what exactly you want to compare that to, and how you determine true or false is not clear at all.

 

[JamesDLaurie]

Thanks JonMo1,

A little background... We are using the first two characters from the Project code and the first three characters of the activity and concatenating them together to form a code. Our system has a group of predetermined combinations that are acceptable. We would like to have those returned as "Capitalized" If the combination is not in the acceptable list, we would like it returned as "Not Capitalized".

Using the previous example... Let's say that the predetermined list contains both AB123 and DE987 as acceptable codes. However, LQ784 would not be on the list. Therefore, in the formula after the concatenation, we need the formula to compare the results to the system combinations in the formula (ex. IF(AB123, DE987, ...)). If it is true, then return "Capitalized". If not, return "Not Capitalized". I will key the predetermined value combinations into the formula.

Thanks,

James

 

[Joe4]

OK, if you have this "list" somewhere, you should be able to do this with a VLOOKUP function.
So, if you have a single column list of all the acceptable values, let's select that list and name it "MyRange" (see: http://www.contextures.com/xlNames01.html).
Then, for an entry in cells A2 and B2, we can use this VLOOKUP formula to return your desired text:

=IF(ISERROR(VLOOKUP(LEFT(A2,2)&LEFT(B2,3),MyRange,1,0)),"Not Capitalized","Capitalized")

If you are not familiar with VLOOKUP, see: https://www.techonthenet.com/excel/formulas/vlookup.php