MATCH

[Joe]

Say I have the following information:

Column A B C (want these results)
Part Delivery Longest Delivery
123 14 123
456 10 789
789 14 223
123 8
321 9
223 14
123 stk

What I'm trying to accomplish is have a formula enter all
the parts with 14 and list them starting in row C (it
should look like C above). I have tried =LOOKUP(LEFT(TRIM
(H4),2),'Priced BOM Summary Page'!S3:S82,'Priced BOM'!
D382) and =MATCH(LEFT(TRIM(H4),2),'Priced BOM'!
S3:S82,0) but have errors. Can this be done.

TIA
Joe

 

[Don Guillett]

Have a look at data>filter>auto filter

 

[immanuel]

With your data beginning on row 2, enter the following array formula in C2:

=IF(SMALL(IF($B$2:$B$8=14,ROW($B$2:$B$8),65537),ROW()-1)<65537,INDIRECT("A"&
SMALL(IF($B$2:$B$8=14,ROW($B$2:$B$8),65537),ROW()-1)),"")

* Remember to Ctrl-Shift-Enter the formula.

Then drag down to the end of your list.

Replace "$B$8" with the end of your real range.

/i.

 

[Joe]

That's kinda what I want the results to look like but, I
want to be able to use the value in one cell from another
tab to lookup the data below and then copy the
information back onto the first tab. Could this only be
done with auto filter and setting up a macro to
automatically do it? But how would I get the value into
the auto filter?

Thanks for your help and input
Joe