Vlookup Error

Question

I was advised to use Vlookup to solve a problem but it's not working. I have a long series of data for various stocks. I want a function to search for the name of that stock in a table, in a certain range, then return the 'rank' of that stock into my current cell. This is how Vlookup is supposed to work.

But I get these results in my first column A. The result should be that it looks up Walmart (C2), finds it in cols (C2:D4), and returns the rank '1' (D2).

Instead. this only works right for my second row, where it seeks Exxon (C3) and finds its rank is '2' (D3). I get screwy results for the identical formula

for the other rows. I tried sorting the Col C names, small to large, so they are in order in the second table. Again Vlookup only works for Exxon! Help!

Col.A Col.B Col. C Col.D

Lookup??		Row Labels	Rank		Equation Used		Row 1
WALMART INC		WALMART INC	1		=VLOOKUP(C2,C$2:D$4,D2,FALSE)	Row 2
2		EXXON MOBIL CORP	2		=VLOOKUP(C3,C$2:D$4,D3,FALSE)	Row 3
#REF!		GE AEROSPACE	3		=VLOOKUP(C4,C$3:D$4,D4,FALSE)	Row 4
								Row 5
2		EXXON MOBIL CORP	2		=VLOOKUP(c6,c$6:d$8,d6,FALSE)	Row 6
#REF!		GE AEROSPACE	3		=VLOOKUP(c7,c$6:d$8,d7,FALSE)	Row 7
WALMART INC		WALMART INC	1		=VLOOKUP(c8,c$6:d$8,c8,FALSE)	Row 8

Answer 1

The 3rd argument should be 2 in all cases:

=VLOOKUP(C2,C$2:D$4,2,FALSE)

=VLOOKUP(C3,C$2:D$4,2,FALSE)

=VLOOKUP(C4,C$2:D$4,2,FALSE)

Etc.

Answer 2

Thanks. Got it now! It works. Column number 2, not D2 as the third argument.

Answer 3

Thanks. I needed to put Column No. 2 instead of "D2". Got it.

Answer 4

The three formulas are all looking up C2 for some reason.

Answer 5

Thanks, Hans. But now I get this result when they are sorted alpha, but errors when they are not sorted alpha. It should pull ranks 2,3,1 from column D.