vlookup error

[sephiroths816]

I tried reading other posts to see if has been human error on my part
as to why my vlookup function seems to have reached a limit and/or
returns random values.

I've already checked the field from which the formula should cover
(for instance: A1:A250). I have over 200 rows and will progressively
grow as I continually add more data to throughout the year.

Now, what I'm seeing happen after the 49th row is that it cycles
around or randomly chooses a value completely off from the actual
data.

Take for instance: (search for Name5):C1=(UserDefined)

12 Name1
..
..
..
39 Name 2
..
..
..
48 Name3
49 Name4
50 Name5

The formula I tried: =VLOOKUP(C1,A1:A250,2)

So long as I stretch the formula's search field to cover to the 49th
row, it is all true and correct. 49 is and correct and accordingly,
Name4. But, when I extend the search field to the 50th row, it returns
Name1. If I stretch it to the search 54 rows, it returns Name2.

I also tried other methods such as: (being that column A is the number
column and column B are the names. C1=User Defined for the names)

=INDEX(A1:A250,MATCH(C1,B1:B250)+0

I still get a random value returned to me again.

If I could find out where I'm going wrong from anyone, I would greatly
appreciate it, thanks.

 

[Guest]

The table array in your lookup formula is incorrect. You are looking at
column A only and then trying to return a value from column 2 of the table
when there is no column 2.

What I think you should be doing if I have understood correctly is using a
Vlookuo formula of:-

=VLOOKUP(C1,A1:B250,2)

In the above formula column b is the second coulmn of the table and the
value will be returned form there. With the correct formula there is no 49
column limit.

Mike

 

[sephiroths816]

My mistake Mike,

I meant to say that I did put:

=VLOOKUP(C1,A1:B250,2)

So I still need to find out why I'm not returning the values I need.

Sorry for the confusion.

 

[Guest]

try:

=VLOOKUP(C1,A1:B250,2,0)

The last parameter (0 or FALSE) doesn't rquire the data to be sorted.
Omitting this requires the data in first column (A) to be sorted and hence
you will get your "random" results.

HTH

 

[Guest]

See my previous reply.

My mistake Mike,

I meant to say that I did put:

=VLOOKUP(C1,A1:B250,2)

So I still need to find out why I'm not returning the values I need.

Sorry for the confusion.

 

[sephiroths816]

Thank you Toppers. Once again, I seemed to have overlooked the small
yet obvious reason.