SOLVED

Vlookup errors with parenthetic numbers

Copper Contributor

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. 

@Mr_McSquirrelly 

 

can you share the workbook?

@Yea_So 

 

 I've attached the worksheet

@Mr_McSquirrelly 

 

what is the url for the web download

@Yea_So That's the URL https://www.sports-reference.com/cfb/schools/miami-fl/2021-schedule.html

 

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.

@Mr_McSquirrelly 

 

Created KeyPhrase table, added column for Official Name in Website

Formula:

 

=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),"")

 

Yea_So_0-1634520359678.png

 

The link where I got the Official Name in Website:

School Index | College Football at Sports-Reference.com

 

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.

@Mr_McSquirrelly 

 

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

Yea_So_0-1634525474791.png

column 5

Yea_So_1-1634525540165.png

column 6

Yea_So_2-1634525566317.png

column 7

Yea_So_3-1634525584906.png

column 8

Yea_So_4-1634525607226.png

 

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

Yea_So_0-1634526920563.png

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.

@Mr_McSquirrelly 

 

There ya go:

Yea_So_0-1634528153751.png

 

cheers

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

@Mr_McSquirrelly 

 

which excel version do you have?

Excel 2016

@Mr_McSquirrelly 

 

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)
Solution

@Mr_McSquirrelly 

 

Here you go:

Yea_So_0-1634534618515.png

A shorter version of the same formula optimized:

Yea_So_0-1634541939891.png

 

 

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

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Mr_McSquirrelly 

 

Here you go:

Yea_So_0-1634534618515.png

A shorter version of the same formula optimized:

Yea_So_0-1634541939891.png

 

 

View solution in original post