Vlookup - splitting the list

[Guest]

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

 

[Dave Peterson]

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().

 

[Guest]

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 here: ...
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

 

[Dave Peterson]

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.)

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 here: ...
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

 

[Guest]

Thanks a lot David and warm greets from Poland! It works