Formula Options

Katich

Board Regular
Joined
Jan 22, 2008
Messages
112
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.


ZoneNameName 2CDN TagResult 1Result 2
MP1xyzxyz000002322830 MP1-SP-I-2-IMP1-SP-I-2-I

SP xyzxyz000002322830 SP-I-2-ISP-I-2-IMP1-SP-I-2-I

<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,767
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
MAybe this

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

Katich

Board Regular
Joined
Jan 22, 2008
Messages
112
MAybe this

Code:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 22, 2008
Messages
112

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Are you sure that you have matching CDN numbers? As it works for me

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Zone</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Name 2</td><td style="font-weight: bold;;">CDN</td><td style="font-weight: bold;;">Tag</td><td style="font-weight: bold;;">Result 1</td><td style="font-weight: bold;;">Result 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">MP1</td><td style=";">xyz</td><td style=";">xyz</td><td style="text-align: right;;">000002322830</td><td style=";">MP1-SP-I-2-I</td><td style=";">MP1-SP-I-2-I</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">SP</td><td style=";">xyz</td><td style=";">xyz</td><td style="text-align: right;;">000002322830</td><td style=";">SP-I-2-I</td><td style=";">SP-I-2-I</td><td style=";">MP1-SP-I-2-I</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">asse 1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=E2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=IF(<font color="Blue">LEFT(<font color="Red">E2,2</font>)="MP","",INDEX(<font color="Red">D$2:E$3,MATCH(<font color="Green">1,INDEX(<font color="Purple">(<font color="Teal">D$2:D$3=D2</font>)*(<font color="Teal">LEFT(<font color="#FF00FF">E$2:E$3,2</font>)="MP"</font>),0</font>),0</font>),2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Katich

Board Regular
Joined
Jan 22, 2008
Messages
112

ADVERTISEMENT

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

Excel 2013/2016
ABCDEFG
1ZoneNameName 2CDNTagResult 1Result 2
2MP1xyzxyz000002322830MP1-SP-I-2-IMP1-SP-I-2-I
3SPxyzxyz000002322830SP-I-2-ISP-I-2-IMP1-SP-I-2-I

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
asse 1

Worksheet Formulas
CellFormula
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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
What do you get in G3?
 

Katich

Board Regular
Joined
Jan 22, 2008
Messages
112
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
F3 just has
=E3
 

Watch MrExcel Video

Forum statistics

Threads
1,123,502
Messages
5,602,041
Members
414,497
Latest member
guitarmanz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top