| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
please help with lookup function
i am trying to do a lookup function for a chart i use.
what i am trying to do is create a list of counts that i do on parts for work. at the moment i can look for a count using a part number and it will list it with the other details but i need it to look for all counts for the same part number and list them all. i have lookup part , rev , desc , date , card , on hand , count , adjust (X) if i enter part number into (x) then it will show the information in the count dated (31/10) from the table below. This table could contain 1000's of parts so just want to be able to pick out certain numbers to avoid scrolling through them all part , rev , desc , date , card , on hand , count , adjust 1234 01 cap 31/10 123 5 4 -1 1234 01 cap 01/10 129 10 11 +1 4567 03 nut 01/10 140 20 19 -1 the function i currently use is =VLOOKUP($B$7,$B$13:$J$108,2,FALSE) this is function in colunm (2) rev i enter the part number into a box and it looks for the part number but stops at the first one it comes to. Ideally i need it to show all counts on the same part number possibly up to 4 counts per year. i tried to attach a copy of the sheet but can't, hope this is enough information to go by for you very clever people out there |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
please help with lookup function
i went to website and tried the example shown but could not get it to work.
I am not very experienced with lookup functions and maybe i am missing something. It does look like what i am after but can you offer any more help please "Gord Dibben" wrote: Have a look at this website for info on returning multiples of a lookup value. http://office.microsoft.com/en-us/ex...CL100570551033 Gord Dibben MS Excel MVP On Sat, 3 Nov 2007 09:05:01 -0700, Carl Pro <Carl wrote: i am trying to do a lookup function for a chart i use. what i am trying to do is create a list of counts that i do on parts for work. at the moment i can look for a count using a part number and it will list it with the other details but i need it to look for all counts for the same part number and list them all. i have lookup part , rev , desc , date , card , on hand , count , adjust (X) if i enter part number into (x) then it will show the information in the count dated (31/10) from the table below. This table could contain 1000's of parts so just want to be able to pick out certain numbers to avoid scrolling through them all part , rev , desc , date , card , on hand , count , adjust 1234 01 cap 31/10 123 5 4 -1 1234 01 cap 01/10 129 10 11 +1 4567 03 nut 01/10 140 20 19 -1 the function i currently use is =VLOOKUP($B$7,$B$13:$J$108,2,FALSE) this is function in colunm (2) rev i enter the part number into a box and it looks for the part number but stops at the first one it comes to. Ideally i need it to show all counts on the same part number possibly up to 4 counts per year. i tried to attach a copy of the sheet but can't, hope this is enough information to go by for you very clever people out there |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
please help with lookup function
Example only.....you have data in A1:C7
george 123 12345 pete 234 12346 bob 345 12347 george 456 12348 iggy 567 12349 bob 678 12350 george 789 12351 In A10 enter george In B10 array enter this using CTRL + SHIFT + Enter =INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2) In C10 array enter =INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),3) Drag/copy both down to row 12 to return george 123 12345 456 12348 789 12351 Note Ashish also has a formula that uses error trapping. That would replace the two above in this example Gord On Sun, 4 Nov 2007 09:28:00 -0800, Carl Pro wrote: i went to website and tried the example shown but could not get it to work. I am not very experienced with lookup functions and maybe i am missing something. It does look like what i am after but can you offer any more help please "Gord Dibben" wrote: Have a look at this website for info on returning multiples of a lookup value. http://office.microsoft.com/en-us/ex...CL100570551033 Gord Dibben MS Excel MVP On Sat, 3 Nov 2007 09:05:01 -0700, Carl Pro <Carl wrote: i am trying to do a lookup function for a chart i use. what i am trying to do is create a list of counts that i do on parts for work. at the moment i can look for a count using a part number and it will list it with the other details but i need it to look for all counts for the same part number and list them all. i have lookup part , rev , desc , date , card , on hand , count , adjust (X) if i enter part number into (x) then it will show the information in the count dated (31/10) from the table below. This table could contain 1000's of parts so just want to be able to pick out certain numbers to avoid scrolling through them all part , rev , desc , date , card , on hand , count , adjust 1234 01 cap 31/10 123 5 4 -1 1234 01 cap 01/10 129 10 11 +1 4567 03 nut 01/10 140 20 19 -1 the function i currently use is =VLOOKUP($B$7,$B$13:$J$108,2,FALSE) this is function in colunm (2) rev i enter the part number into a box and it looks for the part number but stops at the first one it comes to. Ideally i need it to show all counts on the same part number possibly up to 4 counts per year. i tried to attach a copy of the sheet but can't, hope this is enough information to go by for you very clever people out there |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
| LOOKUP FUNCTION | Excel Worksheet Functions | |||
| LOOKUP FUNCTION | Excel Worksheet Functions | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
| lookup function 1 | Excel Worksheet Functions | |||
Hybrid Mode
