I have these two list and I am trying to assign values i.e 11,22,33 to the second list that matches values with the first list.
So, for examples in the second list if its "Industrials" it would take value of 11, "Resources" would take value of 33 and so on.
I have tried index match but I believe it won't work in this case. Any help or ideas would be great.
So you may try using any one of the following formulas as shown in the image below
APPROACH ONE - FORMULA USED IN CELL E2
=INDEX($B$2:$B$8,MATCH(LEFT($D2,3)&"*",$A$2:$A$8,0))
APPROACH TWO - FORMULA USED IN CELL F2 (O365 & EXCEL 2021)
=XLOOKUP(LEFT($D2,3)&"*",$A$2:$A$8,$B$2:$B$8,,2)
APPROACH THREE - FORMULA USED IN CELL G2
=VLOOKUP(LEFT($D2,3)&"*",$A$2:$B$8,2,0)
APPROACH FOUR - FORMULA USED IN CELL H2
=LOOKUP(2,1/(LEFT(D2,3)=LEFT($A$2:$A$8,3)),$B$2:$B$8)
* (asterisk) – It represents any number of characters. For example, LEFT($D2,3)&"*" mean Industry, Industrials, Indus, Industrial, etc. ? (question mark) – It represents one single character. For example, R?ck could mean Rock or Rick.~ (tilde) – It is used to identify a wildcard character (~, *, ?) in the text. For example, let’s say you want to find the exact phrase Indus* in a list. If you use Indus* as the search string, it would give you any word that has Industrials at the beginning followed by any number of characters (such as Industrials, Industrialization , etc). To specifically look for Indus*, we need to use ~. So our search string would be Indus~*. Here, the presence of ~ ensures that excel reads the following character as is, and not as a wildcard.