0

i have a vlookup function that is supposed to give me some data from another worksheet. the worksheet contains soccer data, specifically about the european cups of the past 3 years (9-10, 10-11,11-12, 12-13 isnt in there because its not finished yet, allthough dortmund will win ;))

anyway, the data is spread into two worksheets. One contains the total number of points a team has managed to achieve during their matches in all european cup games, as well as some additional data, such as points achieved during qualifying matches, etc. the sheet, in this case, is called '11-12 ranking'.

the other sheet, called '11-12 games' contains ALL european cup matches played in that season, as well as the results of these matches, for ALL teams, not just for one!

so the data looks like this:

11-12 rankings

A              B                C               

team A        pointsTeamA       qualifyingPointsTeamA
team B        pointsTeamB       qualifyingPointsTeamB
team C        pointsTeamC       qualifyingPointsTeamC
  .                .                    .
  .                .                    .
  .                .                    .
  .                .                    .

where pointsTeamA, etc, and qualifyingPointsTeamA, etc. are numbers, and team A, team B, etc. are the names of the Teams, e.g. Real Madrid, etc.

the interesting parts of the other sheet, '11-12 games', contain a list of UNIQUE names of ALL teams which qualified and played in european cups in that season. In the column to the right of these names, I would like to look up the amount of points scored by that team in that season, which are stored, as i said, in the other sheet, '11-12 ranking'. So '11-12-games looks like this.



A                        B                                               C

uniqueTeamName A        =VLOOKUP(A1,'11-12 ranking'!$A$3:$I$292,2)
uniqueTeamName B        =VLOOKUP(A2,'11-12 ranking'!$A$3:$I$292,2)
uniqueTeamName C        =VLOOKUP(A3,'11-12 ranking'!$A$3:$I$292,2)
      .                           . 
      .                           . 
      .                           . 
      .                           .

However, what I actually get In column B of above spreadsheet, is a value matching another row in the lookup spreadsheet, not the value matching the row I want it to match?! I dont know why at the moment. I have checked with a simple if statement that the values I would expect to match across the sheets (e.g. uniqueTeamName A = TeamName A) do match, for example, for the case that would both of these values are 'Madrid', 'Barcelona', or whatever. So I don't know, at the moment, why it isn't looking up these values correctly. Is anyone able to help on this? :) Thanks a lot in advance!! Cheers, and if anyone is interested in the results I am getting from this, I can post them later! Thanks,

Chris

|improve this question
5

You need to use FALSE for the range_lookup value.

From VLOOKUP help

Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. For more information, see Default sort orders.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

|improve this answer
  • 1
    Ah, thanks! I wasn't aware that this was that easy! :) – Hans Fortmüller Apr 10 '13 at 19:36
  • hmm, for some values, this worked, but for others, it didn't. E.g., I have a team 'Aab Aalborg' (from DK:)), and for it, I'm getting #N/A. I have sorted the lookup table in ascending order, as stated in above post, but for some, it's still not working... Apparently, as stated above, it is not finding an exact match, even though, again, the strings in both tables match, as I have also tested with an if-statement ( not sure if it is the best way to compare strings, but it seems to work). Anyway, I will look into this. In the meantime, If anyone has any additional advice, that would be great! :) – Hans Fortmüller Apr 10 '13 at 19:52
  • I was recommended to use false not to sort the data. It's working for some values and not others? – Brad Patton Apr 10 '13 at 19:54
  • ah, my bad, never mind, i had another mistake in the spreadsheet, so anyway, thanks for the help, it solved the actual question I had! – Hans Fortmüller Apr 10 '13 at 19:57
  • yep@ Brad, i used false, I just ALSO sorted the data! Anyway, it works now, thanks for your help Brad!! :) – Hans Fortmüller Apr 10 '13 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.