I have some names from g6 to g9 on "Sheet 1" of my excel:
I have these names in column B of another sheet called "Names" but in a different order, and also without the "(-2)":
In column L of the same sheet, I have a bunch of numbers which are the points corresponding to the names.
Back to sheet1 of my excel, i did
=VLOOKUP(I6, Names!$B$5:$L$55, 11, FALSE)
This worked for the first two names, printing their scores, 0.75 and 0.5, but then it said #N/A for the last two names as they had a number next to them, making them not match the names from Sheet 1.
I then tried to do
=VLOOKUP(I6, TrainerRanking!$B$5:$L$55, 11, TRUE)
Now, there were no more #N/A's. However, for the first name - Z Purton, it should have printed 0.75 but it printed 0
is there a way to fix this?
Try using TEXTBEFORE( )
=VLOOKUP(TEXTBEFORE(I6,"(",,,1,I6), Names!$B$5:$L$55, 11, FALSE)
Or,
=XLOOKUP(TEXTBEFORE(I6:I9,"(",,,1,I6:I9), Names!B5:B55, Names!L5:L55, "")
The TEXTBEFORE( ) function has 2 mandatory parameters and 4 optional parameters, the parameters which shows within square brackets are called optional.
So that said, the TEXTBEFORE( ) Function syntax is
=TEXTBEFORE(text,delimiter,[instance_num],[match_mode],[match_end],[if_not_found])
therefore in the above formula I have used the function as
=TEXTBEFORE(I6:I9,"(",,,1,I6:I9)
hope you can relate it now. Where I6:I9 is my text, ( is my delimiter, instance_num and match_mode is nothing here taken, while the match_end is 1 & if not found is same I6:I9.
You can also read the MS Documentation here
Alternative approach for all users, as mentioned in comments by P.b Sir.
=VLOOKUP(IFERROR(LEFT(I6,FIND("(",I6)-1),I6),Names!$B$5:$L$55,11,0)