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 18 2021 09:01 AM
Oct 18 2021 09:06 AM
@Mr_McSquirrelly But that shouldn't be too difficult to handle. But perhaps I underestimate the problem.
Oct 18 2021 04:07 PM
@Riny_van_Eekelen @Mr_McSquirrelly
I tried pq against the list in:
School Index | College Football at Sports-Reference.com
it does not include the actual team name, where referred name in one column and actual team name in another column
Have to manually click on Alabama for example to get
so it cannot be down using power query, I tried unless @Mr_McSquirrelly has a direct link to a table I described above.
cheers
Oct 29 2021 01:59 PM
Oct 29 2021 02:38 PM
Oct 29 2021 05:48 PM
Thank you for your help. Attached spreadsheet. I just need Vlookup to find data for teams with scores in columns B and C in top range and ignore teams with no scores in column B and C. Thanks in advance.
Oct 29 2021 06:42 PM - edited Oct 29 2021 06:45 PM
is my lookup range, A2:F83? or ?? you forgot to mention the lookup range
is my lookup range in the formula correct or not?
Oct 29 2021 06:49 PM
The range is a named range Teampower_AVGSEASON which has all the season averages of every team. That's why Vlookup is looking up all those numbers in bottom range. It's just the way the data comes from the web. It lists all the teams for the entire season instead of teams that have completed games. I need Vlookup to differentiate between the team with scores in upper range and just look up those teams in named range Teampower_AVGSEASON
Oct 29 2021 06:56 PM - edited Oct 29 2021 06:58 PM
I need to see the layout of Teampower_AVGSEASON
make sure I see the ABCD columns and 1235 rows
Meaning:
so i can see the target ranges
Oct 29 2021 06:58 PM
Oct 29 2021 07:07 PM
Oct 29 2021 07:17 PM
I'm still trying to understand what it is that you are doing in the worksheets, maybe you can help enlighten me with for example:
Toronto raptors formula in this image:
you're looking at column 4 which is:
FORECAST!$H$126:$R$155
which is OAV04 <=== what does OAV04 mean? why is the formula looking for the value in column 4 I need to understand that
Oct 29 2021 07:29 PM - edited Oct 29 2021 07:47 PM
Don't change anything yet!
Maybe I'll need to explain to you excel and formulas.
think of a sheet as a continent
Poindif is a continent and you are in it
Forecast is another continent that is your target you're pointing your missiles at a particular target in Forecast continent so you'll need a map of Forecast continent.
excel is a triangulation equation tool
when you do a look up whether it is VLOOKUP or INDEX+MATCH or some other lookup function, you'll need the exact location of the value "the target"
so you'll triangulate, think of the columns and rows as longitude and latitude.
or you can narrow the triangulation by FILTERING the filter function.
for example:
=FILTER($B$180:$B$256,(ISNUMBER(FIND(A95,$A$180:$A$256)))*(ISNUMBER(FIND(D95,$D$180:$D$256))))
this FILTER function will filter the value range under 2 conditions
condition number 1 it will find the value Toronto raptors in range A180 to A256
condition number 2 it will find the value Orlando Magic in D189 to D256
where it intersects is where the value is "the crosshairs"
do you follow?
so you have 3 elements 1 the range 2 the location of the 2 opposing teams
because there are 3 Toronto raptors instances in column B and likely 3 instances of Orlando magic as well in column D so in column B has to be true as well as column D so to get the target latitude (horizontal) alignment is between B and D whereas the range is the longitude (vertical alignment) so there's the 1 range, 2 the 1st latitude alignment column B Toronto raptors, 3 the 2nd latitude alignment column D Orlando Magic a triangle.
So the question is what is our Target. I'll need the range column and row and expected value returned so we can decide which function to use whether it be a VLOOKUP, an INDEX+MATCH, or a FIND+FILTER function
Oct 29 2021 07:47 PM
OAV4 is the Counta Average for the last four scores in a dynamic range. All the team tabs pull data from a website which updates the scores every day after the games are played. OAV4 is the AVERAGE of the last 4 games. It seems complicated. I was just hoping Vlookup could somehow differentiate from the team with scores and the teams with blank scores because I need the average of just the first five teams, and not the average scores of every team in the bottom range.
Oct 29 2021 07:59 PM - edited Oct 29 2021 08:12 PM
that adds a complexity of the formula because:
You know which column the target value is located
because you need 5 values then the FIND+FILTER function is a candidate
because you need the top 5, you're looking at RANK.EQ() or COUNTIFS() or MAX/MIN or MAXIFS and MINIFS functions as candidates to your solution
so show me the lookup range and explain why this column will be chosen now and if a different column is chosen later on as the game progresses and because why what is the element that changes why it will change later on for example:
with INDEX function we can define multiple ranges to index, and based on the circumstances of whether we're looking for OAV04 or DAV04 etc we can employ a MATCH function to choose which range (area) we will be looking at example:
INDEX([RANGE1],[RANGE2],[RANGE3],,,IF(date()=[some date] is true, then MATCH(OAV04 or DAV04, [ROW RANGE] (will tell match which column/range to look at,[exact match])
something like that. or we can use an INDEX+MATCH+FIND+FILTER kind of solution
where INDEX will tell FIND+FILTER which area to FIND + FILTER then MAXIFS or MINIFS the resulting FILTERED range to find the target.
but if you need all 5 we can employ a table which will list the top 5 and do a VLOOKUP on each. Very complex indeed which will probably involve using power query.
VLOOKUP is only useful in simple one array range, + one latitude parameter if the lookup value is in the middle of the table either employ a VLOOKUP look to the left or INDEX+MATCH because VLOOKUP without the nested IF({1,0} ... can only return values up to the right and the lookup value must be on the left most latitude wise.
Oct 29 2021 08:21 PM
Oct 29 2021 08:25 PM - edited Oct 29 2021 08:32 PM
I would recommend looking for a solution which would rank and isolate the top 5 dataset:
Ranking Method Choices in Power Query - The Excelguru BlogThe Excelguru Blog
on here, you're project is very challenging i would recommend @Riny_van_Eekelen @Hans Vogelaar @Sergei Baklan @OliverScheurich @L z. they would love to help you with your puzzle especially because you are not stingy sharing your data set.
Oct 29 2021 08:44 PM
@Yea_So I will contact them. Is it possible to hide the team name in column A when the the score cells in column b and c are blank?
Oct 29 2021 08:47 PM
Worry about that when your project is complete as it may create an unintentional dead end to the complete solution where you will find difficult to find the source of the unintentional bug to your project.
Oct 29 2021 10:07 PM
@Mr_McSquirrelly DOne! VLOOKUP(MID(A18,6,5),LEFT(Table1,5),2,0)