using vlookup to retreave multi data??

[jonodo]

Hi guys,

Is it posible to use vlookup to retreive multipule Job No. from a list?
eg ,

Job No.-------Part No.----------- Qty.
g33116 ------- bla----------------- 5
bla------------ bla-----------------4
33116g--------bla-----------------3
bla------------bla-----------------2
33116---------bla-----------------1

I would like to pull out all the Job No's with 33116 in them onto a new worksheet to hand out as a report.If anyone would point me in the right direction i could possible save up to 45min a day!!!

Thanks
Jono

[duane]

something like this will add up line items (column B in this case) for entries incolumn a containing "33116"

=SUMPRODUCT((NOT(ISERROR(SEARCH(33116,A1:A10,1))))*(B1:B10))

[jonodo]

Im not entierly sure what exactly the equation does but ive broken it down and used it to state if a job number is there or not using the iserror function(true or false), now how can pull the relevant jobs out of the list?

Thanks for the reply!

[duane]

ok, how about this, somewhere in an empty column put

=if(NOT(ISERROR(SEARCH(33116,A1,1)),1,0) in row 1 and copy down

then do a filter on this column selecting the rows with 1 in them

[jonodo]

Pretty damb smart hey!!! This will definetly work!!
I will have to spend some time refining it but i think it is safe to say the company i work for should owe you some money!

Thanks
Jono

[daddylonglegs]

A way to do this directly would be to use this formula in B41 copied down

=IF(COUNTIF(B$20:B$32,"*"&E$37&"*")>ROW()-ROW(B$41),INDEX(B$20:B$32,SMALL(IF(ISNUMBER(FIND( E$37,B$20:B$32)),ROW(B$20:B$32)-ROW(B$20)+1),ROW()-ROW(B$41)+1),),"")

confirmed with CTRL+SHIFT+ENTER

then you could use a VLOOKUP to find the relevant date (assuming each job number is unique)