Formula Options

[Katich]

Hello,

I need some help with finding the most efficient formula. This could have other questions that lead after this but my first issue is below. Tag 1 and Tag 2 are my end results i need to come up with. So if the Tag column has an "MP" in the beginning, then my Result 1 will equal the Tag number. If the Tag column has an "SP" in the beginning, then my Result 1 will be what is in the Tag column. However, if it does have an "SP", then the Result 2 column needs to look through the spreadsheet and find another "CDN" number that matches but has an "MP" in the beginning and return the "MP" Tag #. Below I have an example of what my results should be. I hope this makes sense.


[TABLE="width: 706"]
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 706"]
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Zone[/TD]
[TD]Name[/TD]
[TD]Name 2[/TD]
[TD]CDN[/TD]
[TD] Tag[/TD]
[TD]Result 1[/TD]
[TD]Result 2[/TD]
[/TR]
[TR]
[TD]MP1[/TD]
[TD]xyz[/TD]
[TD]xyz[/TD]
[TD="align: right"]000002322830[/TD]
[TD] MP1-SP-I-2-I[/TD]
[TD]MP1-SP-I-2-I[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD]SP [/TD]
[TD]xyz[/TD]
[TD]xyz[/TD]
[TD="align: right"]000002322830[/TD]
[TD] SP-I-2-I[/TD]
[TD]SP-I-2-I[/TD]
[TD]MP1-SP-I-2-I[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

MAybe this

=IF(LEFT(A2,2)="MP",E2,"MP1-"&E2)

 

[Katich]

MAybe this

=IF(LEFT(A2,2)="MP",E2,"MP1-"&E2)

Only problem is that the "CDN" column is made up of 200 some rows with different "CDN" numbers. Within those "CDN" numbers, there may be duplicates but they may have tag numbers. So I feel like I need some kind of lookup or index formula.

 

[Fluff]

How about
=IF(LEFT(E2,2)="MP","",INDEX(D$2:E$3,MATCH(1,INDEX((D$2:D$3=D2)*(LEFT(E$2:E$3,2)="MP"),0),0),2))

 

[Katich]

How about
=IF(LEFT(E2,2)="MP","",INDEX(D$2:E$3,MATCH(1,INDEX((D$2:D$3=D2)*(LEFT(E$2:E$3,2)="MP"),0),0),2))

If I use the formula in my Results column for the tag that has "MP", it works but I can't seem to get it to work for the SP one.

 

[Fluff]

Are you sure that you have matching CDN numbers? As it works for me

Excel 2013/2016
	A	B	C	D	E	F	G
1	Zone	Name	Name 2	CDN	Tag	Result 1	Result 2
2	MP1	xyz	xyz	000002322830	MP1-SP-I-2-I	MP1-SP-I-2-I
3	SP	xyz	xyz	000002322830	SP-I-2-I	SP-I-2-I	MP1-SP-I-2-I
asse 1

Cell Formulas
Range	Formula
F2	=E2
G2	=IF(LEFT(E2,2)="MP","",INDEX(D$2:E$3,MATCH(1,INDEX((D$2:D$3=D2)*(LEFT(E$2:E$3,2)="MP"),0),0),2))

 

[Katich]

Are you sure that you have matching CDN numbers? As it works for me

Excel 2013/2016

	A	B	C	D	E	F	G
Zone	Name	Name 2	CDN	Tag	Result 1	Result 2
MP1	xyz	xyz	MP1-SP-I-2-I	MP1-SP-I-2-I
SP	xyz	xyz	SP-I-2-I	SP-I-2-I	MP1-SP-I-2-I

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]000002322830[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]000002322830[/TD]

</tbody>

asse 1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=E2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(LEFT(E2,2)="MP","",INDEX(D$2:E$3,MATCH(1,INDEX((D$2:D$3=D2)*(LEFT(E$2:E$3,2)="MP"),0),0),2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I have it working in Row 2 but when i use the formula in Row 3, the "Result 2" doesn't work. I did drag the formula down. Also, most times there may be many CDN numbers in between each other so the same CDN numbers will not always appear on top of each other as in this case.

 

[Fluff]

What do you get in G3?

 

[Katich]

What do you get in G3?

Ok, my G3 now works. I had the formula referencing the wrong cell. Though, what formula do you have in F3? I tried using the same formula but it puts the "MP" in front of the "SP".

 

[Fluff]

F3 just has
=E3