this formula works "=VLOOKUP($I$4,'Data Entry'!$A:$AJ,36,FALSE)" but when the
36 is changed to any higher number it does not work. example
"=VLOOKUP($I$4,'Data Entry'!$A:$AJ,37,FALSE)". is 36 the max number of
'lookups' i can have?

What is "doesn't work"?

Error message? Nothing? Incorrect result?

The 36 is the column index number.

I tried with a table and got returns from up to 60 as a column index number.

Couldn't be bothered going any further just for testing.

Something else is going on.

Do you actually have a column 37 in your Lookup table?


Gord Dibben MS Excel MVP

On Thu, 7 Sep 2006 19:12:01 -0700, davidson191
wrote:

this formula works "=VLOOKUP($I$4,'Data Entry'!$A:$AJ,36,FALSE)" but when the
36 is changed to any higher number it does not work. example
"=VLOOKUP($I$4,'Data Entry'!$A:$AJ,37,FALSE)". is 36 the max number of
'lookups' i can have?

In The cell it has "#REF!", and yes i do have a column 37. it goes up to 60.
37 - 60 are showing the #REF!.

"Gord Dibben" wrote:

What is "doesn't work"?

Error message? Nothing? Incorrect result?

The 36 is the column index number.

I tried with a table and got returns from up to 60 as a column index number.

Couldn't be bothered going any further just for testing.

Something else is going on.

Do you actually have a column 37 in your Lookup table?


Gord Dibben MS Excel MVP

On Thu, 7 Sep 2006 19:12:01 -0700, davidson191
wrote:

this formula works "=VLOOKUP($I$4,'Data Entry'!$A:$AJ,36,FALSE)" but when the
36 is changed to any higher number it does not work. example
"=VLOOKUP($I$4,'Data Entry'!$A:$AJ,37,FALSE)". is 36 the max number of
'lookups' i can have?

i can send you a copy of the workbook, so you can look at what i messed up.

"davidson191" wrote:

In The cell it has "#REF!", and yes i do have a column 37. it goes up to 60.
37 - 60 are showing the #REF!.

"Gord Dibben" wrote:

What is "doesn't work"?

Error message? Nothing? Incorrect result?

The 36 is the column index number.

I tried with a table and got returns from up to 60 as a column index number.

Couldn't be bothered going any further just for testing.

Something else is going on.

Do you actually have a column 37 in your Lookup table?


Gord Dibben MS Excel MVP

On Thu, 7 Sep 2006 19:12:01 -0700, davidson191
wrote:

this formula works "=VLOOKUP($I$4,'Data Entry'!$A:$AJ,36,FALSE)" but when the
36 is changed to any higher number it does not work. example
"=VLOOKUP($I$4,'Data Entry'!$A:$AJ,37,FALSE)". is 36 the max number of
'lookups' i can have?

Some thoughts on your orig. post ..

this formula works "=VLOOKUP($I$4,'Data Entry'!$A:$AJ,36,FALSE)"
but when the 36 is changed to any higher number it does not work.

That's because the table array 'Data Entry'!$A:$AJ covers cols A to AJ only,
a total of 36 cols.

"=VLOOKUP($I$4,'Data Entry'!$A:$AJ,37,FALSE)".
is 36 the max number of 'lookups' i can have?

Just extend the table array to say: 'Data Entry'!$A:$BZ and it'll work till
78
Eg: =VLOOKUP($I$4,'Data Entry'!$A:$BZ,37,FALSE)

Or, go the full show to cover all 256 cols, viz use:
=VLOOKUP($I$4,'Data Entry'!$1:$65536,37,FALSE)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,
thank you both of your formulas worked. is there a way to have it so if on
the look up sheet there is a blank colum(colum 37 in one row does not have a
value) to have the other sheet show nothing instead of a "0" i dont know if
this makes sence.(2sd example. you have 10 lookups b2-b11 for one set of data
b6 is empty. on the formula sheet where that value is to show up it puts a
0.)

"Max" wrote:

Some thoughts on your orig. post ..

this formula works "=VLOOKUP($I$4,'Data Entry'!$A:$AJ,36,FALSE)"
but when the 36 is changed to any higher number it does not work.

That's because the table array 'Data Entry'!$A:$AJ covers cols A to AJ only,
a total of 36 cols.

"=VLOOKUP($I$4,'Data Entry'!$A:$AJ,37,FALSE)".
is 36 the max number of 'lookups' i can have?

Just extend the table array to say: 'Data Entry'!$A:$BZ and it'll work till
78
Eg: =VLOOKUP($I$4,'Data Entry'!$A:$BZ,37,FALSE)

Or, go the full show to cover all 256 cols, viz use:
=VLOOKUP($I$4,'Data Entry'!$1:$65536,37,FALSE)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Good eyes Max.

AJ was overlooked by yours truly.

I guess that was the "something else" that was going on<g


Gord

On Fri, 8 Sep 2006 15:46:24 +0800, "Max" wrote:

Some thoughts on your orig. post ..

this formula works "=VLOOKUP($I$4,'Data Entry'!$A:$AJ,36,FALSE)"
but when the 36 is changed to any higher number it does not work.

That's because the table array 'Data Entry'!$A:$AJ covers cols A to AJ only,
a total of 36 cols.

"=VLOOKUP($I$4,'Data Entry'!$A:$AJ,37,FALSE)".
is 36 the max number of 'lookups' i can have?

Just extend the table array to say: 'Data Entry'!$A:$BZ and it'll work till
78
Eg: =VLOOKUP($I$4,'Data Entry'!$A:$BZ,37,FALSE)

Or, go the full show to cover all 256 cols, viz use:
=VLOOKUP($I$4,'Data Entry'!$1:$65536,37,FALSE)