lookup using multiple criteria

[DPR]

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.

 

[DPR]

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.

 

[T. Valko]

Try this array formula** :

With your inputs made on row 15.

=INDEX(E2:E4,MATCH(1,(A2:A4=A15)*(B2:B4=B15)*(C2:C4=C15)*(D24>D15),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

 

[Mike H]

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)*(D212>=N1),),),)

Mike

 

[Mike H]

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)*(D212>=N1),),),)

Mike