Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter the results of a list based on a previous vlookup against the same list | Excel Worksheet Functions | |||
Can I add a drop-down list to a Vlookup? | Excel Worksheet Functions | |||
Drop Down List + VLOOKUP | Excel Worksheet Functions | |||
vlookup returns list? | Excel Worksheet Functions | |||
An unsorted list in vlookup | Excel Worksheet Functions |