| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
looking up a value only if it returns a value in another list
I have two data tables in Excel 2007.
Table A holds a bill of materials with parent and child parts laid out suitable for use with vlookup. Table B holds a selected list of raw materials which are child parts. I want to lookup a parent part from a cell and search for one of its child parts in table A if the result of the search matches one of the raw materials in table B. I can do simple vlookups but this is outside my knowledge. Can anyone please point me in the right direction? Thanks -- Richard |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
looking up a value only if it returns a value in another list
Can you post a small example and the expected result?
-- Biff Microsoft Excel MVP "Richard" wrote in message ... I have two data tables in Excel 2007. Table A holds a bill of materials with parent and child parts laid out suitable for use with vlookup. Table B holds a selected list of raw materials which are child parts. I want to lookup a parent part from a cell and search for one of its child parts in table A if the result of the search matches one of the raw materials in table B. I can do simple vlookups but this is outside my knowledge. Can anyone please point me in the right direction? Thanks -- Richard |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
looking up a value only if it returns a value in another list
bill of material;
parent child child child widget1 nylon carton1 tape widget2 rubber carton2 film list of acceptable values nylon rubber if the result of looking up a cell with widget1 returns a value which is in the list of acceptable values, return value, if not return "error" So the first cell with the formula would be targeted to return an acceptable value from a list of raw materials such as nylon or rubber say. Another cell would then have a similar formula but with say an acceptable value concerning cartons. Each set of child parts would only ever contain one raw material in the acceptable value list, never multiples. The database is extracted using Microsoft Query from a stock control program. There are about 4500 parent parts and upto a maximum of 8 child parts per parent. Thanks, hope this gives you an idea of what I am stuck on. (Is there a way I can post a spreadsheet example if this explanation is not clear) Best regards -- Richard "T. Valko" wrote: Can you post a small example and the expected result? -- Biff Microsoft Excel MVP "Richard" wrote in message ... I have two data tables in Excel 2007. Table A holds a bill of materials with parent and child parts laid out suitable for use with vlookup. Table B holds a selected list of raw materials which are child parts. I want to lookup a parent part from a cell and search for one of its child parts in table A if the result of the search matches one of the raw materials in table B. I can do simple vlookups but this is outside my knowledge. Can anyone please point me in the right direction? Thanks -- Richard . |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
looking up a value only if it returns a value in another list
If I understand what you want, try this...
This data in the range A2:D3 - widget1 nylon carton1 tape widget2 rubber carton2 film A10 = parent lookup value = widget1 B10:B11 = list of acceptable values Enter this formula in C10 and copy down to C11: =IF(COUNT(MATCH(B10,INDEX(B$2:D$3,MATCH(A$10,A$2:A $3,0),0),0)),B10,"error") -- Biff Microsoft Excel MVP "Richard" wrote in message ... bill of material; parent child child child widget1 nylon carton1 tape widget2 rubber carton2 film list of acceptable values nylon rubber if the result of looking up a cell with widget1 returns a value which is in the list of acceptable values, return value, if not return "error" So the first cell with the formula would be targeted to return an acceptable value from a list of raw materials such as nylon or rubber say. Another cell would then have a similar formula but with say an acceptable value concerning cartons. Each set of child parts would only ever contain one raw material in the acceptable value list, never multiples. The database is extracted using Microsoft Query from a stock control program. There are about 4500 parent parts and upto a maximum of 8 child parts per parent. Thanks, hope this gives you an idea of what I am stuck on. (Is there a way I can post a spreadsheet example if this explanation is not clear) Best regards -- Richard "T. Valko" wrote: Can you post a small example and the expected result? -- Biff Microsoft Excel MVP "Richard" wrote in message ... I have two data tables in Excel 2007. Table A holds a bill of materials with parent and child parts laid out suitable for use with vlookup. Table B holds a selected list of raw materials which are child parts. I want to lookup a parent part from a cell and search for one of its child parts in table A if the result of the search matches one of the raw materials in table B. I can do simple vlookups but this is outside my knowledge. Can anyone please point me in the right direction? Thanks -- Richard . |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
looking up a value only if it returns a value in another list
Thank you very much for that.
I will try it out and let you know how I get on. Much appreciated -- Richard "T. Valko" wrote: If I understand what you want, try this... This data in the range A2:D3 - widget1 nylon carton1 tape widget2 rubber carton2 film A10 = parent lookup value = widget1 B10:B11 = list of acceptable values Enter this formula in C10 and copy down to C11: =IF(COUNT(MATCH(B10,INDEX(B$2:D$3,MATCH(A$10,A$2:A $3,0),0),0)),B10,"error") -- Biff Microsoft Excel MVP "Richard" wrote in message ... bill of material; parent child child child widget1 nylon carton1 tape widget2 rubber carton2 film list of acceptable values nylon rubber if the result of looking up a cell with widget1 returns a value which is in the list of acceptable values, return value, if not return "error" So the first cell with the formula would be targeted to return an acceptable value from a list of raw materials such as nylon or rubber say. Another cell would then have a similar formula but with say an acceptable value concerning cartons. Each set of child parts would only ever contain one raw material in the acceptable value list, never multiples. The database is extracted using Microsoft Query from a stock control program. There are about 4500 parent parts and upto a maximum of 8 child parts per parent. Thanks, hope this gives you an idea of what I am stuck on. (Is there a way I can post a spreadsheet example if this explanation is not clear) Best regards -- Richard "T. Valko" wrote: Can you post a small example and the expected result? -- Biff Microsoft Excel MVP "Richard" wrote in message ... I have two data tables in Excel 2007. Table A holds a bill of materials with parent and child parts laid out suitable for use with vlookup. Table B holds a selected list of raw materials which are child parts. I want to lookup a parent part from a cell and search for one of its child parts in table A if the result of the search matches one of the raw materials in table B. I can do simple vlookups but this is outside my knowledge. Can anyone please point me in the right direction? Thanks -- Richard . . |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
looking up a value only if it returns a value in another list
Okay brilliant, thank you.
Tried it out and it makes sense. Will extend it now to the actual workbook. Really grateful -- Richard "T. Valko" wrote: If I understand what you want, try this... This data in the range A2:D3 - widget1 nylon carton1 tape widget2 rubber carton2 film A10 = parent lookup value = widget1 B10:B11 = list of acceptable values Enter this formula in C10 and copy down to C11: =IF(COUNT(MATCH(B10,INDEX(B$2:D$3,MATCH(A$10,A$2:A $3,0),0),0)),B10,"error") -- Biff Microsoft Excel MVP "Richard" wrote in message ... bill of material; parent child child child widget1 nylon carton1 tape widget2 rubber carton2 film list of acceptable values nylon rubber if the result of looking up a cell with widget1 returns a value which is in the list of acceptable values, return value, if not return "error" So the first cell with the formula would be targeted to return an acceptable value from a list of raw materials such as nylon or rubber say. Another cell would then have a similar formula but with say an acceptable value concerning cartons. Each set of child parts would only ever contain one raw material in the acceptable value list, never multiples. The database is extracted using Microsoft Query from a stock control program. There are about 4500 parent parts and upto a maximum of 8 child parts per parent. Thanks, hope this gives you an idea of what I am stuck on. (Is there a way I can post a spreadsheet example if this explanation is not clear) Best regards -- Richard "T. Valko" wrote: Can you post a small example and the expected result? -- Biff Microsoft Excel MVP "Richard" wrote in message ... I have two data tables in Excel 2007. Table A holds a bill of materials with parent and child parts laid out suitable for use with vlookup. Table B holds a selected list of raw materials which are child parts. I want to lookup a parent part from a cell and search for one of its child parts in table A if the result of the search matches one of the raw materials in table B. I can do simple vlookups but this is outside my knowledge. Can anyone please point me in the right direction? Thanks -- Richard . . |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
looking up a value only if it returns a value in another list
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Richard" wrote in message ... Okay brilliant, thank you. Tried it out and it makes sense. Will extend it now to the actual workbook. Really grateful -- Richard "T. Valko" wrote: If I understand what you want, try this... This data in the range A2:D3 - widget1 nylon carton1 tape widget2 rubber carton2 film A10 = parent lookup value = widget1 B10:B11 = list of acceptable values Enter this formula in C10 and copy down to C11: =IF(COUNT(MATCH(B10,INDEX(B$2:D$3,MATCH(A$10,A$2:A $3,0),0),0)),B10,"error") -- Biff Microsoft Excel MVP "Richard" wrote in message ... bill of material; parent child child child widget1 nylon carton1 tape widget2 rubber carton2 film list of acceptable values nylon rubber if the result of looking up a cell with widget1 returns a value which is in the list of acceptable values, return value, if not return "error" So the first cell with the formula would be targeted to return an acceptable value from a list of raw materials such as nylon or rubber say. Another cell would then have a similar formula but with say an acceptable value concerning cartons. Each set of child parts would only ever contain one raw material in the acceptable value list, never multiples. The database is extracted using Microsoft Query from a stock control program. There are about 4500 parent parts and upto a maximum of 8 child parts per parent. Thanks, hope this gives you an idea of what I am stuck on. (Is there a way I can post a spreadsheet example if this explanation is not clear) Best regards -- Richard "T. Valko" wrote: Can you post a small example and the expected result? -- Biff Microsoft Excel MVP "Richard" wrote in message ... I have two data tables in Excel 2007. Table A holds a bill of materials with parent and child parts laid out suitable for use with vlookup. Table B holds a selected list of raw materials which are child parts. I want to lookup a parent part from a cell and search for one of its child parts in table A if the result of the search matches one of the raw materials in table B. I can do simple vlookups but this is outside my knowledge. Can anyone please point me in the right direction? Thanks -- Richard . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| choice from a drop down list returns a result to a different cell | Charts and Charting in Excel | |||
| Is there a formula that returns the number at the bottom of a list | Excel Discussion (Misc queries) | |||
| Function returns a list | Excel Worksheet Functions | |||
| vlookup returns list? | Excel Worksheet Functions | |||
| function that returns a list to a single cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
