VLOOKUP table question

[psu1999]

I have a lookup table like the one below (2 columns, 9 rows)

Age Group Category
04 - 06, Baseball
07 - 08 Boys, Baseball
07 - 08 Girls, Softball
09 - 10 Boys, Baseball
09 - 10 Girls, Softball
11 - 12 Boys, Baseball
11 - 12 Girls, Softball
13 - 14 Boys, Baseball
15 - 16 Boys, Baseball

I have a VLOOKUP formula that uses this table to check a value in a cell to see what age group is present and then puts the category in the data results.

The problem is that for some reason it wants to put 11 - 12 Girls as Baseball...this is the only one that acts this way...

Anyone have any ideas?


Here is the formula:

=VLOOKUP(P2,league,2), where league is the named range.

Thank you in advance
Bob

 

[psu1999]

I found by reordering the table helped to solve this...


<TABLE style="WIDTH: 189pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=251 border=0><COLGROUP><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 130pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=173 height=20>04 - 06</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=78>Baseball</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>07 – 08 Boys</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Baseball</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>07 – 08 Girls</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Softball</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>09 – 10 Boys</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Baseball</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>09 – 10 Girls</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Softball</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>11 – 12 Boys</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Baseball</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>11 – 12 Girls</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Softball</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>13 – 14 Boys</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Baseball</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>15 – 16 Boys</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Baseball</TD></TR></TBODY></TABLE>


Not sure why....but i think it is working fine now.

 

[scottylad2]

Using a DV list, i copied your table and made a VLOOKUP basen on a category from the list and it works perfect! not sure what error your getting seem s to be fine for me using DV. I've copied your list/age groups into a list in L15

Excel Workbook
	J	K	L	M
4
5		04 - 06	baseball
6		07 - 08 boys	baseball
7		07 - 08 girls	softball
8		09 - 10 boys	baseball
9		09 - 10 girls	softball
10		11 - 12 boys	baseball
11		11 - 12 girls	softball
12		13 - 14 boys	baseball
13		15 - 16 boys	baseball
14
15		15 - 16 boys	baseball
16
Sheet1

 

[Peter_SSs]

Bob

VLOOKUP actually has 4 arguments and you have only used three. In that case, Excel makes an assumption about the 4th argument. I think your original ordering would have been OK, if you had added the 4th argument as follows:

=VLOOKUP(P2,league,2, 0)

That 4th argument can be 0 or 1 (equivalent to FALSE or TRUE)

Look in Excel's built-in Help for more information and examples about what that 4th argument does.

 

[psu1999]

Thanks for the help....

Works good now...I will look at the help for the 4th argument.

Bob