Oct 17 2021 10:59 AM
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 05:31 PM
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
=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:
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
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 09:24 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.