Vlookup is skipping over some columns

[missnike]

Hello -

Help, please. Vlookup is skipping over some columns as though they don't exist. For instance, if I type in the following formula, =VLOOKUP(J2,$C$2:$G$6,3,FALSE), rather than getting what's in the third column, Vlookup returns what's in Column 10.

Has anyone encountered this before?

I checked the following:
- The cells between Columns 3 and 10 are not blank.
- The cells are all formatted the same way.

Please can anyone help me? An example of a table is pasted below. The first cell is Cell C2. I expected to get "Jill" as a return from the formula above (where J2 = Project 1). Instead, I got '3'.

Why did it skip over the columns that contain "Team A" or "3".

Any help will be appreciated!

[TABLE="width: 351"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Project List[/TD]
[TD]Leaders[/TD]
[TD]Team Members[/TD]
[TD]Groups affected (Only dropdown options can be selected)[/TD]
[TD]Number of people[/TD]
[/TR]
[TR]
[TD]Project 1 [/TD]
[TD]Doris[/TD]
[TD]Jill[/TD]
[TD]Team A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]James[/TD]
[TD]Samantha[/TD]
[TD]Team B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]Martha[/TD]
[TD]Joe[/TD]
[TD]Team C[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]Avinash[/TD]
[TD]Mark[/TD]
[TD]Team D[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ryeera]

Welcome to the forum!

If it did return '3' and not 'Project 3', then that is because you defined the third column of your search-matrix to be the returned value. That is in your case the Number of People and not the Team Members. Try using this formula: =VLOOKUP(J2,$C$2:$G$6,1,FALSE)

 

[missnike]

hello all. Thanks for your help. I now know why I thought it was returning the wrong number. The vlookup was reading a table that had both numbers and dates. It returned a number but the format of the displaying cell was accidentally set to "dates" so I thought it was returning the dates column instead! Thanks a lot for all your help! I'm OK now!!

Cheers!