Look up and match Vendor name in one cell of worksheet from list ofmultiple Vendor names in column o

[insitedge]

I want to Look up and match Vendor name (such as "Baker") written in
one cell of current worksheet (Reference Cell) from list of a variety
of vendor names in column of other worksheet (Search column), and
return those in column cells below Reference Cell, without row
breaks. In other words, the formula searches for all matches and
lists them in consecutive cells.

Also, I want an If statement so that if the reference Cell is blank/
zero and the vendor is not found in the search column of the other
worksheet, no value is returned.

This is what I have so far: =IF(OR($B$5="",'COST WORKSHEET'!
$B6=""),"",INDEX('COST WORKSHEET'!B$6:B$200,MATCH(1,('COST WORKSHEET'!B
$6:B$200=$B$5)*0)))
$B$5 is the Reference Cell, in which I would enter "Baker" as vendor.
'Cost Worksheet"! is the reference column in the range B$6:B$200

 

[Max]

Think what you're after effectively, is to "filter" over a particular
vendor's (possibly multiple) lines in another sheet. This requires a
different approach, as MATCH will only return the 1st match.

Here's a set-up which gives you the goods w/o fuss,
illustrated in this sample:
http://www.freefilehosting.net/download/3dbf6
Filtering lines by vendor in another sheet.xls

Source data is assumed in sheet: COST WORKSHEET (as you posted)
cols A to D, data from row2 down
where key col = col A (Vendor)

In another sheet: z (say)
A simple data validation droplist is created in A2 to enable easy selection
of vendors
(The vendors' names must of course be consistent with what's in the source
sheet's col A)

In C2:
=IF('COST WORKSHEET'!A2="","",IF('COST WORKSHEET'!A2=$A$2,ROW(),""))
Leave C1 blank

In D2:
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX('COST
WORKSHEET'!A:A,SMALL($C:$C,ROWS($1:1))))
Copy D2 to G2. Select C2:G2, fill down to cover the max expected extent of
data in "COST WORKSHEET". Minimize/hide away col C. Cols D to G will return
only the lines for the vendor selected in A2, with all lines neatly bunched
at the top

 

[Max]

New link to same sample, with updated comments in the point formula cells:
http://www.freefilehosting.net/download/3dc1k
Filtering lines by vendor in another sheet.xls