INDEX MATCH based on part of strings

Hi Excel Expert,

Am trying to use index match to return value in column B based on matching 2 first chars on the left in column A.

Col A Col B
POS Majalt
POS Minalt
POS Instinctive
NB Viral
NB Sierra
NB Pierce
CLM Team1
CLM Team2
OS Tr1
OS Tr2
HR Tetra
HR Pentagon
IT penelope

Cell C1 : NBA MyFormula : {=INDEX(B:B,MATCH(LEFT(C1,2),"*"&$A:$A&"*",0))} <<== I need to find the match of NB only in range A:A to return the related values in Col B.

Expected Result:

NB Viral
NB Sierra
NB Pierce


Other than formula stated above, I've also tried with Isnumber(search), etc but all return errors. Can anyone help, pls?

Hi Sixthsense,

Attached is the sample workbook I could came out with to clarify what I want to achieve. I added in another column named Tier to help out the clearer picture. Actually the table with all dept names, teams and Tier acts as a standard reference chart. Particularly, dept NB has 3 sub depts called NBAdmin, NBUnderwriters & NBWorkbasket. But regardless these subdept entry, it should still be able to return the team name based on Tier and recognizing the first 2 letters in front. As for other depts, if the formula only checks the first 2 letters in front, it would still give me the valid output so, I need to ensure that, indexing the team name is based on match criteria on the first 2 letters and Tier.

Hopefully I clarified sufficiently.

Thank you in advance.

Hi Fotis1991,

I've tested your formula. It doesn't hv any error but it only will return if dept name entry is 2 first chars or exact match. e.g. If I enter "PO" or "CL" or "CLM", it will return value accordingly but if I entered "NBA" or NBU", it will return blank.

But what I need is for it to check the first 2 letters only to match, the following letters considered ignored.

Originally Posted by Fotis1991

Do you have all the other repliers in your ignore list...?

Hi Fotis1991,

I don't understand this question. Did I do something wrong? Pls correct me if I do.

Originally Posted by RobertMika

If you need to pull the NB as well
=IFERROR($D$1&" "&INDEX($B$2:$B$14,SMALL(IF(LEFT($A$2:$A$14,2)=$D$1,ROW($B$2:$B$14)-ROW($B$2)+1),ROWS($B$1:B1))),"")
Confirm COntrol+Shift+Enter, drag down
Where D1 is your 2 letter serach term

Hi RobertMika,
I've tried your formula. However it only returns values when only found the first 2 strings that matches in Col A. Also the value it returns is combined with the Team name. Unfortunately, both are not applicable for my requirements.

Hi, to Fotis1991,RobertMika, Sixthsense & all the helpful experts,

I apologize for giving the incorrect impression to all. Actually, my intention to reply 1 by 1 is that I'm trying to respond specifically on the help given by the specific replier so that he/she would know which 1 I've tried and the output I got for evbody's reference. Usually I only respond after I manage to try out the suggested solution. And I tried to respond chronologically whereby I responded to the post reply that is just after mine than followed by the rest down below. Since I am not that fast in testing all the suggested solutions parallely and reply all at once, also due to bad internet connection here, my response only be done one at a time. I didn't notice on Fotis1991 reply as I was focusing to test some other formulas plus I need to settle my other work at my workplace right now.

Above all, I do appreciate the time and solutions given. Those other posts that I didn't or haven't response are those I didn't receive any response at all or asking me to give sample which usually very time consuming to create a mock data as I cannot expose the real data, and I have a challenge to create a mock data which explains better on my case.

Sorry again and moving forward, I supposed to reply to thread rather than reply to the specific replier.

The formula suggested by Sixthsense works as I need. Tqvm Sixthsense.