Formula Options

Katich

Board Regular
Joined
Jan 22, 2008
Messages
154
Office Version
  1. 2019
Platform
  1. Windows
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>
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
MAybe this

Code:
=IF(LEFT(A2,2)="MP",E2,"MP1-"&E2)
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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
asse 1
Cell Formulas
RangeFormula
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))
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top