Vlookup - splitting the list
 

Hello everybody,

could anyone help me?

A1 should be a list of numbers from 2 to over 500001 and B1 should be that
hat i will be looking for entering a number form the first column in an other
worksheet. Cause of only 65000 rows I cannot use vlookup. Is it possible to
split in a "table_array". I'm looking for easiest way, cause i'm the beginner
in using function.

Thanks in advance


--
Daniel from Poland

Vlookup - splitting the list
 

First, you may be able to do what you want by splitting the values over multiple
sheets and using =indirect() in the =vlookup() to determine which sheet should
be looked at.

But before you do this, you may want to explain more what you're trying to do.
If those numbers from 2-500001 return a number, maybe it can be a calculation
instead of an =vlookup().



Daniel wrote:

Hello everybody,

could anyone help me?

A1 should be a list of numbers from 2 to over 500001 and B1 should be that
hat i will be looking for entering a number form the first column in an other
worksheet. Cause of only 65000 rows I cannot use vlookup. Is it possible to
split in a "table_array". I'm looking for easiest way, cause i'm the beginner
in using function.

Thanks in advance

--
Daniel from Poland

--

Dave Peterson

Vlookup - splitting the list
 

Hello Dave,

i am trying to use an AQL worksheet (ISO 2859 Part 1) to make work of my
co-workers easier. i wanna to split this with our quality records.


Q-Level
Quantity S-1 S-2
2 to 8 A A
9 to 15 A A
....
500001 < D E

Here I wanted to type in Quantity ... to get the letter he ...
The Q-Levels (S-1, S-2...) are known and unchanged in my table.

Q-Level
Quantity S-1 S-2
2 A A
3 A A
....
500001 D E


--
Daniel from Poland


"Dave Peterson" wrote:

First, you may be able to do what you want by splitting the values over multiple
sheets and using =indirect() in the =vlookup() to determine which sheet should
be looked at.

But before you do this, you may want to explain more what you're trying to do.
If those numbers from 2-500001 return a number, maybe it can be a calculation
instead of an =vlookup().



Daniel wrote:

Hello everybody,

could anyone help me?

A1 should be a list of numbers from 2 to over 500001 and B1 should be that
hat i will be looking for entering a number form the first column in an other
worksheet. Cause of only 65000 rows I cannot use vlookup. Is it possible to
split in a "table_array". I'm looking for easiest way, cause i'm the beginner
in using function.

Thanks in advance

--
Daniel from Poland

--

Dave Peterson

Vlookup - splitting the list
 

It sounds like you're using =vlookup() with false as the fourth argument.

You may want to try building your table using just the cutoff points:

If you build a table like:
0 A
5 B
10 C
99 D
9999 E
5000001 F
999999999 G

Maybe your formula could look like:
=VLOOKUP(a1,Sheet2!A1:B7,2)
or
=VLOOKUP(int(a1),Sheet2!A1:B7,2)

(You can add other checks to make sure that the value in A1 is valid.)


Daniel wrote:

Hello Dave,

i am trying to use an AQL worksheet (ISO 2859 Part 1) to make work of my
co-workers easier. i wanna to split this with our quality records.

Q-Level
Quantity S-1 S-2
2 to 8 A A
9 to 15 A A
...
500001 < D E

Here I wanted to type in Quantity ... to get the letter he ...
The Q-Levels (S-1, S-2...) are known and unchanged in my table.

Q-Level
Quantity S-1 S-2
2 A A
3 A A
...
500001 D E

--
Daniel from Poland

"Dave Peterson" wrote:

First, you may be able to do what you want by splitting the values over multiple
sheets and using =indirect() in the =vlookup() to determine which sheet should
be looked at.

But before you do this, you may want to explain more what you're trying to do.
If those numbers from 2-500001 return a number, maybe it can be a calculation
instead of an =vlookup().



Daniel wrote:

Hello everybody,

could anyone help me?

A1 should be a list of numbers from 2 to over 500001 and B1 should be that
hat i will be looking for entering a number form the first column in an other
worksheet. Cause of only 65000 rows I cannot use vlookup. Is it possible to
split in a "table_array". I'm looking for easiest way, cause i'm the beginner
in using function.

Thanks in advance

--
Daniel from Poland

--

Dave Peterson


--

Dave Peterson

Vlookup - splitting the list
 

Thanks a lot David and warm greets from Poland! It works :)
--
Daniel


"Dave Peterson" wrote:

It sounds like you're using =vlookup() with false as the fourth argument.

You may want to try building your table using just the cutoff points:

If you build a table like:
0 A
5 B
10 C
99 D
9999 E
5000001 F
999999999 G

Maybe your formula could look like:
=VLOOKUP(a1,Sheet2!A1:B7,2)
or
=VLOOKUP(int(a1),Sheet2!A1:B7,2)

(You can add other checks to make sure that the value in A1 is valid.)


Daniel wrote:

Hello Dave,

i am trying to use an AQL worksheet (ISO 2859 Part 1) to make work of my
co-workers easier. i wanna to split this with our quality records.

Q-Level
Quantity S-1 S-2
2 to 8 A A
9 to 15 A A
...
500001 < D E

Here I wanted to type in Quantity ... to get the letter he ...
The Q-Levels (S-1, S-2...) are known and unchanged in my table.

Q-Level
Quantity S-1 S-2
2 A A
3 A A
...
500001 D E

--
Daniel from Poland

"Dave Peterson" wrote:

First, you may be able to do what you want by splitting the values over multiple
sheets and using =indirect() in the =vlookup() to determine which sheet should
be looked at.

But before you do this, you may want to explain more what you're trying to do.
If those numbers from 2-500001 return a number, maybe it can be a calculation
instead of an =vlookup().



Daniel wrote:

Hello everybody,

could anyone help me?

A1 should be a list of numbers from 2 to over 500001 and B1 should be that
hat i will be looking for entering a number form the first column in an other
worksheet. Cause of only 65000 rows I cannot use vlookup. Is it possible to
split in a "table_array". I'm looking for easiest way, cause i'm the beginner
in using function.

Thanks in advance

--
Daniel from Poland

--

Dave Peterson


--

Dave Peterson