If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
looking up a value only if it returns a value in another list
Ok, still not sure what you want. See if this does it.
I have two data tables in Excel 2007. This is specific to Excel 2007. This data in the range A23 - widget1 nylon carton1 tape widget2 rubber carton2 film A10 = parent lookup value = widget1 B10:B11 = list of acceptable values = nylon, rubber Array entered** : =IFERROR(INDEX(B10:B11,MATCH(1,--ISNUMBER(MATCH(B10:B11,INDEX(B23,MATCH(A10,A2:A3 ,0),0),0)),0)),"error") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. The formula will return whichever "child" value it finds first. For example: widget1 nylon carton1 tape In that data sample it would return nylon widget1 glue carton1 rubber In that data sample it would return rubber widget1 glue carton1 tape In that data sample it would return error widget1 nylon rubber tape In that data sample it would return nylon -- Biff Microsoft Excel MVP "Richard" wrote in message ... Hi there, may we go back to your answer below. The result of the formula must be either nylon or rubber, B10:B11. Whichever one it finds in the range B23 for widget1 or widget2. Your formula only refers to B10 so it only looks for nylon. When it is copied down it refers to B11 so it only looks for rubber. Can it look for nylon or rubber (B10:B11) and return whichever one it finds in the child parts. Hope this makes sense, I kinda need to post a spreadsheet to make it clearer. Many thanks Richard If I understand what you want, try this... This data in the range A23 - 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$3,MATCH(A$10,A$2:A $3,0),0),0)),B10,"error") ... 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. |
#12
|
|||
|
|||
looking up a value only if it returns a value in another list
It does definitely do it on my test sheet!
I will try it out now on the database I have. And I am very grateful for your patience and help. Later I need to get my head round the zeros in the formula in the match section MATCH(A10,A2:A3,0),0),0)),0 Thank you again -- Richard "T. Valko" wrote: Ok, still not sure what you want. See if this does it. I have two data tables in Excel 2007. This is specific to Excel 2007. This data in the range A23 - widget1 nylon carton1 tape widget2 rubber carton2 film A10 = parent lookup value = widget1 B10:B11 = list of acceptable values = nylon, rubber Array entered** : =IFERROR(INDEX(B10:B11,MATCH(1,--ISNUMBER(MATCH(B10:B11,INDEX(B23,MATCH(A10,A2:A3 ,0),0),0)),0)),"error") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. The formula will return whichever "child" value it finds first. For example: widget1 nylon carton1 tape In that data sample it would return nylon widget1 glue carton1 rubber In that data sample it would return rubber widget1 glue carton1 tape In that data sample it would return error widget1 nylon rubber tape In that data sample it would return nylon -- Biff Microsoft Excel MVP "Richard" wrote in message ... Hi there, may we go back to your answer below. The result of the formula must be either nylon or rubber, B10:B11. Whichever one it finds in the range B23 for widget1 or widget2. Your formula only refers to B10 so it only looks for nylon. When it is copied down it refers to B11 so it only looks for rubber. Can it look for nylon or rubber (B10:B11) and return whichever one it finds in the child parts. Hope this makes sense, I kinda need to post a spreadsheet to make it clearer. Many thanks Richard If I understand what you want, try this... This data in the range A23 - 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$3,MATCH(A$10,A$2:A $3,0),0),0)),B10,"error") ... 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. . |
|
Thread Tools | |
Display Modes | |
|
|