Forum Discussion
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 DOne! VLOOKUP(MID(A18,6,5),LEFT(Table1,5),2,0)
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.
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),"")
The link where I got the Official Name in Website:
https://www.sports-reference.com/cfb/schools/
Mr_McSquirrelly I'd suggest you add a column with the "web based names" to your table and perform the VLOOKUP on that column.
- Riny_van_Eekelen, I tried that, but the problem is the number in parentheses, such as (14) Miami (FL). It's a dynamic data range, so currently the team is listed as Miami (FL) because their rank has changed.
Mr_McSquirrelly But that shouldn't be too difficult to handle. But perhaps I underestimate the problem.
