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]

 

[Katich]

ok thanks. I think I have what I need as of now. There is more criteria on the report but this gives me something to work with. Thank you very much. I wish I understood the Index/Match formula better, seems like a very good formula.

 

[Fluff]

Glad to help & thanks for the feedback.

You can find info about various types of index/match here https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/

 

[Katich]

Awesome, thank you and i'll be looking at that site.

 

[Fluff]

You're welcome