Oct 17 2021 10:59 AM
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.
Oct 17 2021 11:22 AM
@Mr_McSquirrelly I'd suggest you add a column with the "web based names" to your table and perform the VLOOKUP on that column.
Oct 17 2021 03:49 PM
Oct 17 2021 04:47 PM
Oct 17 2021 05:09 PM
Oct 17 2021 05:31 PM
@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.
Oct 17 2021 06:29 PM - edited Oct 17 2021 06:31 PM
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:
School Index | College Football at Sports-Reference.com
Oct 17 2021 07:32 PM
Oct 17 2021 07:53 PM - edited Oct 17 2021 08:15 PM
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
Oct 17 2021 08:18 PM
Oct 17 2021 08:36 PM
Oct 17 2021 09:09 PM
Oct 17 2021 09:14 PM
Oct 17 2021 09:20 PM
Oct 17 2021 09:24 PM
Oct 17 2021 09:34 PM
Oct 17 2021 10:10 PM
@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.
Oct 17 2021 10:24 PM - edited Oct 18 2021 12:25 AM
SolutionOct 18 2021 07:11 AM
Oct 17 2021 10:24 PM - edited Oct 18 2021 12:25 AM
Solution
Here you go:
A shorter version of the same formula optimized: