VLOOKUP

A

anthony

i currently been using the Vlookup function this way "
=VLOOKUP(pigs,'Coverage List'!$B$9:$S$5000,B$1,0)" wich has worked great. the
problem is that it only returns information up to 7 cells.( B1:H1)
and i need it to return cells b1:s1...can any one help?
 
T

Toppers

Surely you just change B$1 to reflect which column you want ...or I am
missing something?
 
A

anthony

i have changed B$1 ....but any thing larger then H$1 i get the error message
# value. the array Vlookup seems to hold no more then 7 cells
 
T

Toppers

I tried your formula and it worked OK (in the way I used it). I am not aware
that VLOOKUP has any restriction.

=VLOOKUP(pigs,'Coverage List'!$B$9:$S$5000,18,0)

will retrieve the value from column S

if B$1=18, it will work.

#Value suggests data errors.

I am happy to look at your w/book:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSPAM
 
P

Peo Sjoblom

You can get value errors from either calculation with text or if the
list itself has a value error

What's in B1:S1?
 
A

anthony

how would i get my workbook over to you?

Toppers said:
I tried your formula and it worked OK (in the way I used it). I am not aware
that VLOOKUP has any restriction.

=VLOOKUP(pigs,'Coverage List'!$B$9:$S$5000,18,0)

will retrieve the value from column S

if B$1=18, it will work.

#Value suggests data errors.

I am happy to look at your w/book:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSPAM
Click to expand...
 
A

anthony

i have both lettters and numbers. i have moved the valus around between cells
IE replaceing B1 with S1 ....and eveything works great with the frist 7 cells
but no more that that.
 
P

Peo Sjoblom

Maybe I don't understand but if you a letter in a cell like B1 then you
will get a #REF! error, is that whet you get. I assumed you used the cells
in B1:S1 for indexing the columns you want to retrieve the values from? If
the indexing number is text you will get an error
 
A

anthony

i have indexed from B1:S1
expl all of these work
=VLOOKUP(pigs,'Coverage List'!$B$9:$S$5000,B$1,0)
=VLOOKUP(pigs,'Coverage List'!$B$9:$S$5000,C$1,0
=VLOOKUP(pigs,'Coverage List'!$B$9:$S$5000,D$1,0)
will not work
=VLOOKUP(pigs,'Coverage List'!$B$9:$S$5000,I$1,0)
what i am trying to do is return the row of values that has the the text
pigs in colum B located in go to work sheet titled Coverage List.
this function will do that but any values in "I" and beyound it will not.
i have interchanged values between colums and bothtext and numbers work for
colums C,D,E,F,Gand H ......BUT any values in I,J, K, L, M, and S will not.
 
Top