Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup using multiple criteria
I'm trying to figure out a way to look up a value using multiple input
arguments. I've tried using the index() function but I can't quite get it to do what I want. I'm trying to search through an array of electrical components based on 4 different inputs. My components can be defined by Type, Phase, Voltage, and Rating. I need to use these variables to find a value in the next column which is a part number. My array would appear as follows: Type Phase Voltage Rating Part number A 1 120 20 xxxxxx B 3 240 32 xxxxxx C 3 240 50 xxxxxx .... The first three inputs (Type, Phase, and Voltage) should be exact matches, however the Rating (amps) would most likely not match. I need my function to return the part number of the component with the next higher rating than my input. So if I type in that I want a type B, 3-phase, 240 volt, 35 amp component, it would return the part number for my type B, 3-phase, 240 volt, 50 amp component. Any help would be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup using multiple criteria
Sorry, noticed a typo in my explanation. I meant to have the third component
in my spreadsheet be of the same type as the one above, with only a different rating to distinguish it. "DPR" wrote: I'm trying to figure out a way to look up a value using multiple input arguments. I've tried using the index() function but I can't quite get it to do what I want. I'm trying to search through an array of electrical components based on 4 different inputs. My components can be defined by Type, Phase, Voltage, and Rating. I need to use these variables to find a value in the next column which is a part number. My array would appear as follows: Type Phase Voltage Rating Part number A 1 120 20 xxxxxx B 3 240 32 xxxxxx C 3 240 50 xxxxxx ... The first three inputs (Type, Phase, and Voltage) should be exact matches, however the Rating (amps) would most likely not match. I need my function to return the part number of the component with the next higher rating than my input. So if I type in that I want a type B, 3-phase, 240 volt, 35 amp component, it would return the part number for my type B, 3-phase, 240 volt, 50 amp component. Any help would be appreciated. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup using multiple criteria
Try this array formula** :
With your inputs made on row 15. =INDEX(E2:E4,MATCH(1,(A2:A4=A15)*(B2:B4=B15)*(C2:C 4=C15)*(D2:D4D15),0)) ** 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. Note that if there isn't a next higher amp rating you'll get an error - #N/A -- Biff Microsoft Excel MVP "DPR" wrote in message ... I'm trying to figure out a way to look up a value using multiple input arguments. I've tried using the index() function but I can't quite get it to do what I want. I'm trying to search through an array of electrical components based on 4 different inputs. My components can be defined by Type, Phase, Voltage, and Rating. I need to use these variables to find a value in the next column which is a part number. My array would appear as follows: Type Phase Voltage Rating Part number A 1 120 20 xxxxxx B 3 240 32 xxxxxx C 3 240 50 xxxxxx ... The first three inputs (Type, Phase, and Voltage) should be exact matches, however the Rating (amps) would most likely not match. I need my function to return the part number of the component with the next higher rating than my input. So if I type in that I want a type B, 3-phase, 240 volt, 35 amp component, it would return the part number for my type B, 3-phase, 240 volt, 50 amp component. Any help would be appreciated. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup using multiple criteria
Hi,
This assumes your table is in columns A,b,c, d & e and the 4 values you are looking up are in K1, L1, M1 & N1 =INDEX(E2:E12,MATCH(1,INDEX((A2:A12=K1)*(B2:B12=L1 )*(C2:C12=M1)*(D2:D12=N1),),),) Mike "DPR" wrote: I'm trying to figure out a way to look up a value using multiple input arguments. I've tried using the index() function but I can't quite get it to do what I want. I'm trying to search through an array of electrical components based on 4 different inputs. My components can be defined by Type, Phase, Voltage, and Rating. I need to use these variables to find a value in the next column which is a part number. My array would appear as follows: Type Phase Voltage Rating Part number A 1 120 20 xxxxxx B 3 240 32 xxxxxx C 3 240 50 xxxxxx ... The first three inputs (Type, Phase, and Voltage) should be exact matches, however the Rating (amps) would most likely not match. I need my function to return the part number of the component with the next higher rating than my input. So if I type in that I want a type B, 3-phase, 240 volt, 35 amp component, it would return the part number for my type B, 3-phase, 240 volt, 50 amp component. Any help would be appreciated. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup using multiple criteria
Hmm,
So type is actually irrelevant you need the correct phase (sing1e or 3), the correct voltage and an amperage equal to or higher than the search value and return the part Number. Correct? =INDEX(E2:E12,MATCH(1,INDEX((B2:B12=L1)*(C2:C12=M1 )*(D2:D12=N1),),),) Mike "DPR" wrote: Sorry, noticed a typo in my explanation. I meant to have the third component in my spreadsheet be of the same type as the one above, with only a different rating to distinguish it. "DPR" wrote: I'm trying to figure out a way to look up a value using multiple input arguments. I've tried using the index() function but I can't quite get it to do what I want. I'm trying to search through an array of electrical components based on 4 different inputs. My components can be defined by Type, Phase, Voltage, and Rating. I need to use these variables to find a value in the next column which is a part number. My array would appear as follows: Type Phase Voltage Rating Part number A 1 120 20 xxxxxx B 3 240 32 xxxxxx C 3 240 50 xxxxxx ... The first three inputs (Type, Phase, and Voltage) should be exact matches, however the Rating (amps) would most likely not match. I need my function to return the part number of the component with the next higher rating than my input. So if I type in that I want a type B, 3-phase, 240 volt, 35 amp component, it would return the part number for my type B, 3-phase, 240 volt, 50 amp component. Any help would be appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
Multiple Criteria Lookup | Excel Worksheet Functions | |||
LookUp with multiple criteria | Excel Worksheet Functions | |||
Multiple criteria LOOKUP | Excel Worksheet Functions |