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)
