Vlookup errors with parenthetic numbers


I am trying to make Vlookup find values from a table when values are listed by different names. When I download data from web, my lookup value comes into spreadsheet as (14) Miami (FL), or (10) Texas A&M Aggies. However those same teams are listed in my table as Miami Florida Hurricanes and Texas AM Aggies. VLookup will perform the search and find correct values if I remove the numbers in parentheses, but it causes an error if parenthetic numbers remain in cell. So I need Vlookup to lookup the value (14) Miami (FL) in a table where that value is listed as Miami Florida Hurricanes. The number in parentheses is causing a VLookup N/A# error. Thanks for help in advance.

39 Replies

@Mr_McSquirrelly I'd suggest you add a column with the "web based names" to your table and perform the VLOOKUP on that column. 



can you share the workbook?



 I've attached the worksheet



what is the url for the web download

@Yea_So That's the URL


But I use Query to bring the entire table into the workbook. I've attached the sheet again with the table that comes down through query.



Created KeyPhrase table, added column for Official Name in Website



=IFERROR(VLOOKUP(FILTER(Table1[Official Name in Website],ISNUMBER(FIND(FILTER(KeyPhrases,ISNUMBER(FIND(KeyPhrases,A18))),Table1[Official Name in Website]))),Table1[#All],3,0),"")




The link where I got the Official Name in Website:

School Index | College Football at


That looks great! I tried using that formula to look up the next adjacent cell D8 by changing 3 to 4. But it did not find the value.



It works, I changed the 3 to reference cell D18, so any value i put in D18 will be the column it will look at:

that's column 4


column 5


column 6


column 7


column 8



Here's a refreshed file with 2 other variant formulas:


Take your pick


try testing it again


I'm sorry. I wasn't clear. With your original formula you were able to find the value of C8 and put that value in B18. That's exactly what I was hoping to accomplish. However, I also need the value of D8 to be put in C18, so that B18 and C18 have the value of C8 and D8. I tried adjusting your formula by changing 3 to 4 at the end, hoping the formula would find the value in the fourth column of the table, cell D8.



There ya go:




Wow! Thank you. Is this a formula that require Shift / Control Enter command?



which excel version do you have?

Excel 2016



that formula is not going to work, unless you have 365

we're going to have to re-work the formula since excel 2016 does not the spill feature, we're going to have to do the LEFT(), RIGHT() and MID()
@Yea_So, sorry! I should've clarified before what version I'm using. I will save your formula if I ever upgrade.

@Mr_McSquirrelly since you already work with PowerQuery for the import of the "12 Games Table", why not use PQ also for what is does best. That is, clean-up messy data into something more useful. Rather, than trying to look up inconsistently used school names with formulae, make use of PQ's capability of fuzzy matching with the help of a transformation table. That way, you can merge the list of schools (A18:A23) with matching information from Table1.  Just a suggestion.

best response confirmed by allyreckerman (Microsoft)



Here you go:


A shorter version of the same formula optimized:




@yea-so Thank you for your work on this!