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
|