Excel VLOOKUP true or false issue

Question

I have some names from g6 to g9 on "Sheet 1" of my excel:

• Z Purton
• H Bowman
• C L Chau(-2)
• M F Poon(-7)

I have these names in column B of another sheet called "Names" but in a different order, and also without the "(-2)":

• M F Poon
• H Bowman
• Z Purton
• C L Chau

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?

Answer 1

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)

---

Answer 2

Here is another way:

INDEX($B$10:$B$13,MATCH(IFERROR(LEFT(A3,FIND("(",A3,1)-1),A3),$A$10:$A$13,0))

Adding trim() may be useful as the "(-2) etc may pick up a space prior, so:

INDEX($B$10:$B$13,MATCH(TRIM(IFERROR(LEFT(A3,FIND("(",A3,1)-1)),A3),$A$10:$A$13,0))

Also, this will work in earlier versions of Excel prior to those useful functions like textbefore() and textafter()...