please help with lookup function

[Guest]

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

 

[Gord Dibben]

Have a look at this website for info on returning multiples of a lookup value.

http://office.microsoft.com/en-us/excel/HA012260381033.aspx?pid=CL100570551033


Gord Dibben MS Excel MVP

 

[Guest]

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]

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