Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up and match Vendor name in one cell of worksheet from list ofmultiple Vendor names in column of other worksheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up and match Vendor name in one cell of worksheet from list of multiple Vendor names in column of other worksheet
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 Singapore http://savefile.com/projects/236895 xdemechanik --- "insitedge" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up and match Vendor name in one cell of worksheet from list of multiple Vendor names in column of other worksheet
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add numbers belonging to a vendor | Excel Discussion (Misc queries) | |||
Creating a Vendor ID from Vendor Name... | Excel Worksheet Functions | |||
How do I set up a way to keep track of vendor invoices? | Excel Worksheet Functions | |||
INSERT LINE after Vendor name change | Excel Worksheet Functions | |||
making one material list from mulitple vendor material lists | Excel Worksheet Functions |