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.

Zone Name Name 2 CDN Tag Result 1 Result 2 MP1 xyz xyz 000002322830 MP1-SP-I-2-I MP1-SP-I-2-I SP xyz xyz 000002322830 SP-I-2-I SP-I-2-I MP1-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>

 

[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