Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V lookup with 2 criteria to return results for multiple columns
I have a complicated master inventory spreadsheet from which I need to
generate shorter reports for items currently in use. My column headings are as below: A Product B Color C Volume Used D Manufacturer E Catalog Number F Lot number G Date received H Expiration Date However, we may have a given product in several different colors. All 8 columns need to show up in my report. I would like to be able to use VLOOKUP (or something else that works) to find (for example) Product X in green and then be able to drag the formula to return the rest of the 7 columns as well. If I concatenate first, I can't drag. If I just link the cells then the report gets jumbled up every time somebody sorts the inventory sheet. If I have to type in a separate vlookup formula in each column, I'll still be typing this time next year. I know this really should be done in Access, but unfortunately, that's not an option. Any help would be greatly appreciated. Thanks, JenL |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V lookup with 2 criteria to return results for multiple columns
Your issue description is pretty vague, but it sounds as though advanced
filtering out to get the job done. Have you tried that? Alternatively, you can use MS Query to quickly and easily extract the data you need into another worksheet. Nick Hodge has an excellent write up at this link. Read all the way to the bottom. His technique for pulling data from an Access database into Excel works just as well to pull data from one Excel sheet into another, within the same workbook, if so desired. http://www.nickhodge.co.uk/gui/datam...taexamples.htm "JenL" wrote: I have a complicated master inventory spreadsheet from which I need to generate shorter reports for items currently in use. My column headings are as below: A Product B Color C Volume Used D Manufacturer E Catalog Number F Lot number G Date received H Expiration Date However, we may have a given product in several different colors. All 8 columns need to show up in my report. I would like to be able to use VLOOKUP (or something else that works) to find (for example) Product X in green and then be able to drag the formula to return the rest of the 7 columns as well. If I concatenate first, I can't drag. If I just link the cells then the report gets jumbled up every time somebody sorts the inventory sheet. If I have to type in a separate vlookup formula in each column, I'll still be typing this time next year. I know this really should be done in Access, but unfortunately, that's not an option. Any help would be greatly appreciated. Thanks, JenL |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V lookup with 2 criteria to return results for multiple column
OK, here is a snippet of my actual inventory sheet:
A,B,C,D,E,F Marker, Fluor,Vol (uL),Company,Cat#,Lot No.,Exp Date CD3,APC,10,BD,340440,70286,10/31/08 CD3,PerCP,10,BD,340663,83838,4/30/09 CD3,PE,5,BD,347347,61276,7/31/08 CD4,PC5,10,Immunotech,IM2636,18,9/30/08 CD4,PerCP,10,BD,347324,79417,11/30/08 CD45/14,FITC/PE,10,Immunotech,IM1201U,39,11/26/07 And it goes on for many dozens of rows. There are also many more columns to the right, but that's not data I'm interested in for this purpose. We regularly run an assay using CD45/14 FITC/PE, CD4PerCP, and CD3 APC. I would like to have, as a separate worksheet within the same notebook, a dynamic ("live") sheet that looks like: Marker, Fluor,Vol (uL),Company,Cat#,Lot No.,Exp Date CD45/14,FITC/PE,10,Immunotech,IM1201U,39,11/26/07 CD4,PerCP,10,BD,347324,79417,11/30/08 CD3,APC,10,BD,340440,70286,10/31/08 If I use VLOOKUP alone, I have no guarantee that the correct CD3 or CD4 will be chosen. But, if I use vlookup for a unique marker =vlookup(A2,'inventory'!A5:H200,2,0) I can then drag that formula to the right and the rest of the columns will fill in. I get a sequence that reads: =vlookup(B2,'inventory'!B5:I200,2,0) =vlookup(C2,'inventory'!C5:J200,2,0) etc. If I add a new column before A and concatenate CD3&APC, I can then use vlookup to find the proper entry. BUT, I can't drag the formula anymore and have to type in a separate vlookup formula in each and every column, and for some of our assays this can get to be 100+ rows long. Hence me still sitting here typing next year. Is this less vague now? JenL "Duke Carey" wrote: Your issue description is pretty vague, but it sounds as though advanced filtering out to get the job done. Have you tried that? Alternatively, you can use MS Query to quickly and easily extract the data you need into another worksheet. Nick Hodge has an excellent write up at this link. Read all the way to the bottom. His technique for pulling data from an Access database into Excel works just as well to pull data from one Excel sheet into another, within the same workbook, if so desired. http://www.nickhodge.co.uk/gui/datam...taexamples.htm "JenL" wrote: I have a complicated master inventory spreadsheet from which I need to generate shorter reports for items currently in use. My column headings are as below: A Product B Color C Volume Used D Manufacturer E Catalog Number F Lot number G Date received H Expiration Date However, we may have a given product in several different colors. All 8 columns need to show up in my report. I would like to be able to use VLOOKUP (or something else that works) to find (for example) Product X in green and then be able to drag the formula to return the rest of the 7 columns as well. If I concatenate first, I can't drag. If I just link the cells then the report gets jumbled up every time somebody sorts the inventory sheet. If I have to type in a separate vlookup formula in each column, I'll still be typing this time next year. I know this really should be done in Access, but unfortunately, that's not an option. Any help would be greatly appreciated. Thanks, JenL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup for a value in multiple columns and return a result | Excel Discussion (Misc queries) | |||
Multi-criteria lookup with Multiple results | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
Lookup Multiple Criteria return One answer | Excel Worksheet Functions | |||
Return Multiple Results with Lookup | Excel Worksheet Functions |