VLOOKUP question

[SHood]

Version: v.X
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

OK, I'm back on this NCAA sheet thing now that I'm using the COUNTIF function. Thanks

Now this pool gives points for picking upsets based on each team's seeding. No problem in the first round but more of a problem in rounds after that.

So I created a table for each grouping listing all the teams that could possibly meet in the next game and each teams seeding within that group and had excel calculate the bonus points for each.

So this list has the team name in one column and the points in the column beside it. Then I've used the VLOOKUP function to return the points if any.

My formula looks like

=VLOOKUP(F110,V110:W112,2)

where F110 is the actual winner and V110:W112 is the list and 2 is the column value to be returned.

I've got 16 VLOOKUP's and 16 lists for round 2. 14 of the VLOOKUP's work but for some reason 2 of the VLOOKUP's return the wrong value.

I checked to be certain that the team names were exactly the same in the bracket and in the list.

I read that there is a TRUE or FALSE argument, that is not required, telling what to do if a match were not found. So I added "FALSE" to those formulas and it returned the correct bonus point value, (in this case 0).

My questions are

What could be the difference between the functions that work and those that don't ?

Why would adding FALSE cause the function to return a zero ? The book says if it can't find a match FALSE will return #N/A.

Thanks

 

[JE McGimpsey]

My formula looks like

=VLOOKUP(F110,V110:W112,2)

where F110 is the actual winner and V110:W112 is the list and 2 is the column
value to be returned.

I've got 16 VLOOKUP's and 16 lists for round 2. 14 of the VLOOKUP's work but
for some reason 2 of the VLOOKUP's return the wrong value.

I checked to be certain that the team names were exactly the same in the
bracket and in the list.

I read that there is a TRUE or FALSE argument, that is not required, telling
what to do if a match were not found. So I added "FALSE" to those formulas
and it returned the correct bonus point value, (in this case 0).

My questions are

What could be the difference between the functions that work and those that
don't ?

Why would adding FALSE cause the function to return a zero ? The book says if
it can't find a match FALSE will return #N/A.

If the range_lookup argument is set to TRUE, VLOOKUP assumes that the
first column of the list is sorted, and will return the first value
"less than or equal to" the lookup value, which for strings, means it
finds the lookup value or the value just before one that would sort
alphabetically after the lookup value. So with

A B
1 aardvarks 1
2 bluejays 3
3 cheetahs 2
4 dingos 7
5 zebras 8

then

=VLOOKUP("foxes",A1:B5,2,TRUE)

will return 7, since dingos sorts before foxes, and zebras sorts after.

Conversely

=VLOOKUP("foxes",A1:B5,2,FALSE)

will return #N/A, since "foxes" is not found in the list.

 

[SHood]

OK

Thanks